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

[@krzysztofs8535]

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

[@Csharp-video-tutorialsBlogspot]

Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.

[@adduxe]

I believe you can go GROUP BY EmailDomain instead teacher. P.S. I was just offered a job yesterday after watching only 22 of your SQL videos! The extra notes you included in the video were actually included in the interview! (Whew!) THANK YOU SO MUCH!!!!!! Hat's off to you teacher!

[@tejamarneni]

@adduxe You cannot use alias in a Group by clause.

[@lucylee9457]

Very useful videos. It is so kind of you filming all these informative and educational videos. Thank you very much.

[@Trzbne]

5 days without watching your videos... Hard and long week. Already here, enjoying your lesson :-) Thank you very much!

[@trytofindareasonwhy]

Oh man that last part is tough. kudos to any who fully understand this topic

[@rasikadhanapala5354]

Excellent teaching. Thank you very much 👌

[@vigneshvikey9392]

Very useful videos, excellent teaching thank-you so much sir keep going and upload more videos 💐💐

[@caothanhlong7577]

Wow very easy to understand! Thank you so much.

[@maheshgirish6071]

Thank you so much Venkat sir great teaching

[@programmeseekho2795]

Thank You Sir Very helpful Video..Very Nice Sir

[@aliazad1118]

As usual perfect. Best tutorial

[@hrushinathkale1372]

Very Nice video sir. thank you so much for this series.

[@nolimit167]

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)?

[@sohanyennu3750]

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

[@navinkrishnan9419]

thank you!

[@vkgupta]

In your last example, Can I use Alias name as EmailDomain After group by?

[@AVD072]

It is very helpful

[@jagadeeshperuri2527]

Nice explanation with short time sequence...is there any possibility for ASP.NET MVC tutorials

[@eldadnevo1742]

Perfect videos, can you upload the sample database?

[@ashokbachina7646]

Venkat..you are awesome!!!

[@BoxerDogs]

Good explanation. Thank you for posting this.

[@satyaprakashsingh1644]

thanks very much.... its very useful, i am studying from this tutorial

[@afifkhaja]

Very well explained. Thanks!

[@devarakondasankararao3802]

Great explanation Sir

[@diegomarin5598]

Thank you so much! it really helps a lot!!

[@prashantx19]

How can I separate the full_name column into the separate columns EX- I have a table with concatenated full name and I want to display that into separate columns like first_name, middle_name, last_name Prashant M sonawane Amit S patil

[@oksanaveretiuk1653]

Many thanks!

[@suprajacheppala2294]

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

[@kaushikbhadani]

very very nice...Thank you..

[@rupeshthangella2059]

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

[@flaeski]

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

[@piyasidey2117]

Thank You Very Much Sir...

[@piyushamohanty2671]

Hello Sir, its really very video and use full However, there is one question from my end..... Here you've used all the domain names of same length so you've done as it is. Let me know what will be the query when the domain names are of different length in the column? I really need your help sir...

[@ASHOKSHARMA-yn4gl]

Great information, thank you so much :) Ashok

[@asmitapatil8087]

Do you have any session on synonyms in sql

[@palcolon]

Thank you my good sir

[@richardmirabelli512]

Is it possible to use LEFT, RIGHT AND SUBSTRING in CASE expressions. For example, I want to say (Case when left(column1,5) = left(column2,5) then column 3 else null end). Is this doable? If not are there alternatives to this? Thanks

[@nimishajain8860]

thank u so much for explaining :)

[@Csharp-video-tutorialsBlogspot]

+Nimisha Jain You are very welcome. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you kzbin.infoplaylists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. kzbin.info/www/bejne/r2ibYYCtnb5qZtU If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. kzbin.info If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat

[@pravallikakiran9198]

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('[e-mail]',charindex('@','[e-mail]')+1,len('[e-mail]')) 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.

[@sokkimchhoin8621]

thank so much

[@sivaboyidi9939]

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.....

[@dunnymusic]

very helpful thanks

[@anilgarlapati8998]

Thank you

[@aamirhalai4907]

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

[@hasinmahjabeen7252]

Cool!

[@pradeepdhamija569]

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?

[@Raghav54321]

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

[@stutitehri993]

very important functions

[@gauravverma6533]

Nice tutorial

[@harshadgurav9281]

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

[@redietalemayehu640]

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))

[@birdsdepartment]

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.

[@ravindrababuravi5495]

Explained very well, thank you. Can you please explain how we can divide a value\text into columns if it consist of multiple underscores? like abc_123h_xyz, 2345x_TZ_5546.

[@wenjiegan597]

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?

[@sunilkumar-hy4yt]

Hi, As per SQL Execution order Group By Executes first then Select, so it cannot pick the alias in Group By. Where as Order By executes after Select so alias can be used in order by.

[@davinder1910singh]

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?

[@harshag1335]

Sir how do i display a name which is starting from 6th position (right end) and i want to display only 4 characters..

[@thuythanhhainh6932]

Hi everyone, Can I ask a question regards to the minute 14:00 please? When I tried to write the Group By statement like: Group By EmailDomain -> system stated I had an error because of the invalid column name "EmailDomain". I don't understand why that's it? Can anybody help me explain it pleaseeeeeee.

[@chanchalarya983]

sir how can I add $ in sum(total_amt) plz tell

[@gregorym7598]

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

[@sanasafinaz8549]

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

[@newaribabu]

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?

[@technologyforalleasyway135]

hello sir, how to get a digit from right side after 4 digit, like from a number 101456678, want to get 4 but using right, please help

[@TiengtrungXiXi]

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

[@72sh]

Like 'google' from 'google.com' ? If that is ur question then SELECT SUBSTRING( EMAILADDRESS, CHARINDEX('@', EMAILADDRESS) + 1, LEN(EMAILADDRESS) - (CHARINDEX('@', EMAILADDRESS)+ 2) - LEN(CHARINDEX('.', EMAILADDRESS)) ) FROM [Person].[EmailAddress]

[@MixTapeYT1]

SELECT SUBSTRING(Email,0,CHARINDEX('@',Email)) from tblPerson

[@gregorym7598]

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...

[@SaikatBhadury]

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

[@sanjaychndrn5]

10:05 SELECT SUBSTRING('[e-mail]', CHARINDEX('@','[e-mail]')+1, LEN('[e-mail]')-CHARINDEX('@','[e-mail]')+1) Output: gmail.com

[@mranonymous1812]

caaarindex

[@tkalpana6]

Sir If i need to find the names in the email i mean left characters of email(ex:[e-mail] I need ravi instead of domain name) what will be the expression.

[@ivangrgic4281]

select Left(email,charindex('@',Email)-1) fromTBLemployee -> for left string

[@neyon7824]

FirstName - Jesus LastName - Manual Scavenger

[@MrFaheemalig]

DECLARE @full_path VARCHAR(500)= 'C:\Users\Michael\Documents\Microsoft SQL Server\MyScript.sql'; we want to pull "MyScript.sql " this one from this full path but I need dynamically can you please answer this

[@AnandKumar-dc2bf]

Its better if you remove such very old videos and keep only new ones atlest 5 years old ...

[@callmecheese4696]

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

[@codingstream572]

is he from mycodeschool....?

[@arshianazneen2012]

In the length part subtracting full email - starting till @ should b done to get the string from bbb.com but here we r subtracting 11-7 ie full string - starting from @