Combining PARSENAME() and REPLACE(), you get Excel’s Text to Column. The issue with PARSENAME is that it could only work with “.”. Therefore you need REPLACE to change whatever delimiter it is, to “.”
|
1 2 3 4 5 6 7 |
DECLARE @STR VARCHAR(128) SELECT @STR ='a~b~c~d' SELECT @STR, PARSENAME(REPLACE(@STR,'~','.'),4) AS One, PARSENAME(REPLACE(@STR,'~','.'),3) As Two, PARSENAME(REPLACE(@STR,'~','.'),2) As Three, PARSENAME(REPLACE(@STR,'~','.'),1) As Four |
|
1 2 |
(No column name) One Two Three Four a~b~c~d a b c d |
Another funny thing with PARSENAME is that 4 means the first, which is ‘a’. Thanks Microsoft. 🙂