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

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

[@krzysztofs8535]

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

[@lucylee9457]

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

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

[@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 👌

[@aliazad1118]

As usual perfect. Best tutorial

[@sohanyennu3750]

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

[@caothanhlong7577]

Wow very easy to understand! Thank you so much.

[@maheshgirish6071]

Thank you so much Venkat sir great teaching

[@vigneshvikey9392]

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

[@hrushinathkale1372]

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

[@sps-pm]

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

[@programmeseekho2795]

Thank You Sir Very helpful Video..Very Nice Sir

[@diegomarin5598]

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

[@ashokbachina7646]

Venkat..you are awesome!!!

[@BoxerDogs]

Good explanation. Thank you for posting this.

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

[@afifkhaja]

Very well explained. Thanks!

[@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?

[@sanjaychndrn5]

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

[@AVD072]

It is very helpful

[@oksanaveretiuk1653]

Many thanks!

[@devarakondasankararao3802]

Great explanation Sir

[@jagadeeshperuri2527]

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

[@vkgupta]

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

[@navinkrishnan9419]

thank you!

[@piyasidey2117]

Thank You Very Much Sir...

[@eldadnevo1742]

Perfect videos, can you upload the sample database?

[@kaushikbhadani]

very very nice...Thank you..

[@ASHOKSHARMA-yn4gl]

Great information, thank you so much :) Ashok

[@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

[@palcolon]

Thank you my good sir

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

[@suprajacheppala2294]

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

[@asmitapatil8087]

Do you have any session on synonyms in sql

[@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

[@flaeski]

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

[@Raghav54321]

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

[@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

[@aamirhalai4907]

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

[@dunnymusic]

very helpful thanks

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

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

[@anilgarlapati8998]

Thank you

[@rupeshthangella2059]

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

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

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

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

[@harshadgurav9281]

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

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

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

[@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?

[@hasinmahjabeen7252]

Cool!

[@stutitehri993]

very important functions

[@gauravverma6533]

Nice tutorial

[@sokkimchhoin8621]

thank so much

[@chanchalarya983]

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

[@harshag1335]

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

[@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?

[@tuhoctiengtrunghichinese]

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

[@sanasafinaz8549]

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

[@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

[@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

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

[@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

[@neyon7824]

FirstName - Jesus LastName - Manual Scavenger

[@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

[@callmecheese4696]

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

[@mranonymous1812]

caaarindex

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

[@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 @