CODEWITHSUNDEEP
sql-serversql-server-2008
Luka
Luka

Reputation: 189

MS SQL split values in same table

Good day

I have :

TableX
Column1
John Smith 007
Tera Name 111
Bob Eva 554

I need 

TableX
Column1                              Column2
John Smith 007                         007
Tera Name 111                          111
Bob Eva 554                            554

I created code but not work. I think there must be join to recognise columns.

ALTER TABLE [dbo].[TableX]
ADD Column2 varchar (50); 

UPDATE [dbo].[TableX] SET
    Column1=Column2
WHERE select SUBSTRING([Column1], PATINDEX('%[0-9]%', [Column1]
), LEN([column1]))

Thanks for help

Upvotes: 0

Views: 44

Answers (2)

sanil gurung
sanil gurung

Reputation: 41

Assuming required part length = 3

UPDATE [dbo].[TableX] SET
column2 = RTRIM(right(column1, CHARINDEX('/', column1) +3))

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If the number you want to extract is always at the end, then you can use:

PATINDEX('%[^0-9]%', REVERSE(Column1))

to get the index of the first character that is not a number, starting from the end.

So, to extract the number you can use:

RIGHT(Column1, PATINDEX('%[^0-9]%', REVERSE(Column1)) - 1)

Hence, the UPDATE will look like this:

UPDATE [dbo].[TableX] 
SET Column2 = RIGHT(Column1, PATINDEX('%[^0-9]%', REVERSE(Column1)) - 1)

Demo here

Upvotes: 1

Related Questions