I have a table with this data:
shyam banarjee rahul khanna priya kumari rohit srivastava
I just want first names, like this:
shyam rahul priya rohit
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can use "Substrig" and "charindex" functions to get what you want.
See this code as a demo on how to use it:
declare @name varchar(30) set @name = 'shyam banarjee' select SUBSTRING(@name,1,charindex(' ',@name)-1)
In your case, instead of being a variable in the select, it will be your column name.
You could also look at "computed column" if ever you want to have a new column that would only contains the firstname.
This will allow you to choose which part by simply filtering on RowNum. It will work no matter how many values you have...(assuming data is in a table called #temp)
SELECT FIELD1,Value,ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELD1) RowNum FROM #Temp T CROSS APPLY STRING_SPLIT(FIELD1, ' ') T1
Specifically, this query will give you the first names. If you want last name, change it to RowNum=2 on last line.
SELECT Value FROM ( SELECT FIELD1, Value, ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELD1) RowNum FROM #Temp T CROSS APPLY STRING_SPLIT(FIELD1, ' ') T1 ) a WHERE RowNum=1