Menu
I have a column which has FirstName and LastName together. I'm writing a report to separate the FirstName And LastName. How do I get the FirstName and LastName separated in T-SQL?
396
Using SUBSTRING Function The CHARINDEX function returns the starting position of a specified expression in a character string. The CHARINDEX function was used to look for the space that separates the first name from the last name (CHARINDEX(' ', @FullName)).
substr((replace(fullname,(substr(fullname,1, instr(fullname,' '))),'')),instr((replace(fullname,(substr(fullname,1, instr(fullname,' '))),'')),' ')+1) Last_Name from fullnames; Note---> fullnames is a table name and fullname is a column name.
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.
Used WHERE CHARINDEX(' ',ltrim(rtrim(fieldname))) = 0 which works fine ! @huMptyduMpty - You got it. =0 will give you items with one word, >0 will give you items with two words -- and yes, if you have leading in or trailing spaces those will need to be removed.
Assuming the FirstName is all of the characters up to the first space:
SELECT SUBSTRING(username, 1, CHARINDEX(' ', username) - 1) AS FirstName, SUBSTRING(username, CHARINDEX(' ', username) + 1, LEN(username)) AS LastName FROM whereever The easiest way I can find to do it is:
SELECT SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) AS FirstName, REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)) AS LastName FROM [PERSON_TABLE] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
