user3311323 user3311323 - 2 years ago 85
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
Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download