LEFT, RIGHT, CHARINDEX and SUBSTRING functions in sql server   Part 23

LEFT, RIGHT, CHARINDEX and SUBSTRING functions in sql server Part 23



In this video we will learn about the commonly used built-in string functions in SQL server
1. LEFT()
2. RIGHT()
3. CHARINDEX()
4. SUBSTRING()

A real time example of using string functions

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Nguồn: https://amthucgiadinh.vn

Xem thêm bài viết khác: https://amthucgiadinh.vn/am-nhac/

Tagged In:

39 Comments

  1. Hello Sir How are you? Hope ou are fine. Please tell me how to use alias "Total" in a query such as (english+maths) as Total,(Total/MaxMarks)*100 as Percentage.

  2. or we can easily use this query to get the last email id and then group them up. What do you think?
    Select RIGHT(Email,Len(Email) – CharIndex('@',Email)), Count(*)

    From tblEmployee
    Group by RIGHT(Email,Len(Email) – CharIndex('@',Email))

  3. Hi Sir, Can you make video regarding Partitions in Sql?

  4. at time location 10:02 if you just hardcode 100 instead of 7 then I got the same results. I even tested if it added 93 spaces. But it did not. Is there a issue with that idea which I do not know. Doing this will make it faster?

  5. How and why is possible for example Sara to with
    select CHARINDEX('@', Email)-2, Email
    from tblEmployee
    be 2
    and LEN of her Email is 11 here…so 11-2=9………………………
    AND
    select (LEN(Email) – CHARINDEX('@', Email)-2) from tblEmployee
    be 5
    so 11-2=5
    I got it and know what happened, but can you explain this situation why SQL Server look like (11 – 4) – 2 = 5 when is obviously and logically 11 – 2 = 9
    because CHARINDEX('@', Email)-2 giving output 9
    If you have time and understand what I'm asking explain, even if I realized what happened here, explain why is this happening like this…when is particularly like I said 11-2=9

  6. Venk, I was playing little with this and look what I found:
    select SUBSTRING(Email, CHARINDEX('@',Email)-5,

    (LEN(Email) – CHARINDEX('@', Email)-2)) Name
    from tblEmployee
    group by SUBSTRING(Email,CHARINDEX('@', Email)-5,

    (LEN(Email) – CHARINDEX('@', Email)-2))
    Well isn't it interesting…

  7. 6:53 “we want to start the position sex”

  8. How can I apply the same syntax on ORACLE SQL Developers? This one is in MS SQL server and I'm using ORACLE SQL SERVER. Oracle doesn't support or recognize CHARINDEX command in Oracle. Anyone please?

  9. Muito boa explicação, parabéns!!!

  10. Great explanation Sir

  11. As usual perfect. Best tutorial

  12. what is the syntax to get before @ for an email for whole table

  13. Wow thank you for this excellent explanation!

  14. how to get middle name from full name . Like in full name we have 'XYZ' and we have to extract z middle name

  15. Wow very easy to understand! Thank you so much.

  16. Good explanation.  Thank you for posting this.

  17. Thanks for the instructional videos! I've learned a ton from you Venkat. One question that i have is that , wouldn't the result still be the same if the last expression/argument in substring was left out? for example, substring(email,charindex('@',email)) would have been the same as substring(email,charindex('@',email), len(email)-charindex('@',email)?

  18. As said in video what are the other ways to get final shown output??

  19. Suppose I have one String as "ABC EFG", now I want to pick the value using RIGHT function till first Space which comes. How Can I do that?

  20. I want to extract strings: "Self Install Kit" in the log file: xxxxxx: xxxxxxx: xxxxxxx, xxxxxxx: xxxxxxx, xxxxx: xxxxx, xxxxxx: xxxxx, xxxxx: xxxxxxxxx, Install Responsibility: Customer, Install Kit: Self Install Kit, xxxxxxx: xxxxx;xxxxxx;xxxxx, xxxxx

    can you help me with the how to locate all the log file that have "Self Install Kit".?

    hope you can help.

    thanks.

  21. Thank You Sir
    Very helpful Video..Very Nice Sir

  22. While Explaining third argument of SUBSTRING() you have used total length of string – till where we do not require.
    But my doubt is while defining the Substring() the second argument itself specifies the start position and so the third argument could be total length of the length.
    select SUBSTRING('sara@aaaa.com',charindex('@','sara@aaaa.com')+1,len('sara@aaaa.com'))
    could give the expedted reply na? why to use (-)minus and all?

    I'm sorry i'm very much new to this IT field so just out of confusion/curiosity i'm posting my doubt.

  23. 5 days without watching your videos… Hard and long week. Already here, enjoying your lesson 🙂 Thank you very much!

  24. Hello Mr.Venkat. Could you please explain the internal query mechanism(query execution process).

    I've a query like this, and i would like to know the process of it…

    select t1.Id,t1.Name,t1.Email,t1.Gender,t1.Languages,t2.name as Name,t3.Name as Name,t4.Name as Name
    from [Table1] t1
    join [table2] t2 on t1.a=t2.Id
    join [table3] t3 on t1.b=t3.Id
    join [table4] t4 on t1.c=t4.Id

    Thanks in advance…..

  25. Hi Venkat could you please explain Substring function with negative (-1) parameter value like select substring(@string,-1,5) etc.

  26. caaarindex

  27. 10:05 SELECT SUBSTRING('sam@gmail.com', CHARINDEX('@','sam@gmail.com')+1, LEN('sam@gmail.com')-CHARINDEX('@','sam@gmail.com')+1)
    Output: gmail.com

  28. What to do if strong contain many same characters and if i want to calculate under of 3rd or 4rth character?

  29. Such a useful concept explained very simply…I really appreciate your efforts!!

  30. Thank you my good sir

  31. Do you can provide the PDF?

  32. Declare @Name nvarchar(50)
    Set @Name='John Deo'
    Declare @FirstLetter nvarchar(1)
    Declare @SecondLetter nvarchar(1)
    Set @FirstLetter=LEFT(@Name,1)
    Set @SecondLetter=SUBSTRING(@Name,CHARINDEX(' ',@Name)+1,1)
    SELECT 'Short Name = '+UPPER(@FirstLetter)+'.'+UPPER(@SecondLetter) AS ShortName

    How To Do Like @Name='John Deo Something' As J.D.S Output

    I Use It.. Thanks Sir

  33. Great information, thank you so much 🙂 Ashok

  34. Second part of STRING functions. Explained in best way as always. Greetings from Poland!

  35. Venkat..you are awesome!!!

  36. Sir If i need to find the names in the email i mean left characters of email(ex:ravi@gmail.com I need ravi instead of domain name) what will be the expression.

  37. Thank You Very Much Sir…

  38. Can you guys tell me how to get the name of emails ?

  39. Hi, Thanks for ur tutorial!
    I have a question. Group by cannot use the alias name in the same query? But Order by is allowed to use?

Leave a Reply