CODEWITHSUNDEEP
sql-server
user8972669
user8972669

Reputation:

SQL patindex substring

I have data imported in one column that i need to substring to seperate the columns in a new table.

However, the spacing is sometimes different as you can see in the middle of the rows below with the 1 2 and the 1 3.

1460165850 346384100 1 2   88534837 1 4 3

1463404020 347588883   1 3 88846400 1 4 5

I have tried the code below but because the 2 and the 1 occupy the same position so when i substring i cant get them into two seperate columns.

substring ([column 0],22,patINDEX('%[0-9]%',substring ([column 0],22,3))) as 'column 1',

substring ([column 0],24,patINDEX('%[0-9]%',substring ([column 0],24,3))) as 'column 2'

Any help appreciated.

Upvotes: 0

Views: 127

Answers (1)

sacse
sacse

Reputation: 3744

try replacing the duplicate spaces using below first in the table and then segregate columns:

REPLACE(REPLACE(REPLACE(Column_Name,' ','<>'),'><',''),'<>',' ')

Example:

select REPLACE(REPLACE(REPLACE('No duplicate      spaces   here   after     executing      this',' ','<>'),'><',''),'<>',' ')

Thanks.

Upvotes: 1

Related Questions