开发者

How to separate string into 3 columns

开发者 https://www.devze.com 2023-01-22 03:26 出处:网络
I have a column of user names. They are as follows: \'firstlastmiddleinitial\' Notice the large spaces betwee开发者_开发知识库n the name parts, these are always a different number of spaces.

I have a column of user names.

They are as follows:

'first       last      middleinitial'

Notice the large spaces betwee开发者_开发知识库n the name parts, these are always a different number of spaces.

Question:

How would I separate first, last, and middleinitial into separate columns (even if the spaces are different for every name)?


WITH t AS
(
    SELECT 'first       last      middleinitial' AS name
)

SELECT 
LEFT(name,CHARINDEX(' ', name)-1) 
,RIGHT(name, CHARINDEX(' ', REVERSE(name))-1)
,LTRIM(RTRIM(SUBSTRING(name,CHARINDEX(' ', name),LEN(name)- CHARINDEX(' ', REVERSE(name))-CHARINDEX(' ', name))))
FROM t
0

精彩评论

暂无评论...
验证码 换一张
取 消