Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2013-09-24 : 13:45:21
|
Hi AllWe capture the firstname of a person along with their middle intial in our contacts table . How do i use the substring function to get just the firstname and omit the middle initialSample DataJennifer KJason RSamuel VI want just the firstname without the space and middle initialRequired FormatJenniferJasonSamuelThanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-24 : 15:19:15
|
[code]LEFT(YourNameColumn,CHARINDEX(' ',YourNameColumn+' '))[/code]This will produce wrong results if there are first names with spaces in them. If there is the possibility that there are spaces at the beginning, you would need to do an LTRIM before using this. |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-09-24 : 17:11:25
|
quote: How do i use the substring function to get just the firstname and omit the middle initial
substring(value, 1, len(value) - 2) |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-09-25 : 01:32:13
|
You can do like this also : Declare @vc_Name VARCHAR(20) = 'Jennifer K'SET @vc_Name = SUBSTRING (@vc_Name,1,CHARINDEX(' ',@vc_Name)-1)SELECT @vc_NameP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-25 : 07:56:52
|
quote: Originally posted by mohan123 You can do like this also : Declare @vc_Name VARCHAR(20) = 'Jennifer K'SET @vc_Name = SUBSTRING (@vc_Name,1,CHARINDEX(' ',@vc_Name)-1)SELECT @vc_NameP.V.P.MOhan
Will break for cases where field doesnt have an initial. tryDeclare @vc_Name VARCHAR(20) = 'Jennifer'SET @vc_Name = SUBSTRING (@vc_Name,1,CHARINDEX(' ',@vc_Name)-1)SELECT @vc_Name to make it work always you need to tweak it likeDeclare @vc_Name VARCHAR(20) = 'Jennifer K'SET @vc_Name = SUBSTRING (@vc_Name,1,CHARINDEX(' ',@vc_Name+ ' ')-1)SELECT @vc_Name ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|