user3311323 user3311323 - 11 months ago 51
SQL Question

Separate number from the fullnames in sql

I am having a table with full name column, but the data in the column also has number attached with it , I need to separate those two in two different columns. This is one example .

create table #Test (ID int, name varchar(50))
insert into #Test values
(1, 'Alice,king (00123)'),
(2, 'John,Maxweell (00226)'),
(3, 'Marry,botam (1226)')

From above table I want
in one column and the
in new column .any help will be appreciated.

vkp vkp

This solution will work if the numbers in the column are always in () and if there is only one occurrence each of ( and ).

 substring(name,1,charindex('(',name)-1) as name
,substring(name,charindex('(',name)+1,charindex(')',name)-charindex('(',name)-1)  as num
from tablename
where charindex('(',name) > 0 and charindex(')',name) > 0