TSQL split column between first and second occurence of delimiter from left

Asked Sep 27 '22 at 12:47

Active Sep 27 '22 at 14:31

Viewed 29 times

I have a column that looks like this

| col1 | 
| -------- | 
| 12345 987456 item thing    | 
| 56455 456842 word word word   | 
| 56845 456584 word word   |

I am looking to select the number between the first and the second occurrence of the space. I.e

| new-col1 | 
| -------- | 
| 987456    | 
| 456842  | 
| 456584  |

As reflected in my data the amount of delimiters(space) and length varies so I cannot use PARSENAME().

I tried to use SPLIT_STRING as well but it is not recognised even though my specs should allow for it

Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Compatibilty 140

edited Sep 27 '22 at 14:31

DineshDB

asked Sep 27 '22 at 12:47

ananame111

2

Why you store multipe information in the same field at all? Clean your data before you insert it, not everytime you take it out. – Tim Schmelter Sep 27 '22 at 12:53
While I agree - I am not in a position to change this. – ananame111 Sep 27 '22 at 13:34
SELECT SUBSTRING(Col1 ,CHARINDEX(' ',Col1,1)+1, CHARINDEX(' ',Col1,CHARINDEX(' ',Col1,1)+1)-CHARINDEX(' ',Col1,1)-1) NewCol1 FROM #TAB – DineshDB Sep 27 '22 at 14:32

0 Answers0