How to do text to column in SQL? i want to seperate string from integer

Transact-SQL
1

so have a table with my customer name and ID. i have million of customers. customer name and ID are concatenated together but i like to separate the ID from customer name. below is a screenshot that shows what i like to accomplish.

i like to pull customer IDs only without the name and parentheses. some customers have a comma between first and last name and some don't. the screenshot below shows how i like to separate the ID from name. (i did this manually)

image

and help is appreciated.

2

Assuming every id will definitely have the ID in it with parentheses (the code needs to be a bit more defensive if not), the easiest way is to find the position of the last of each type of parenthesis and substring between them. Something like:

Drop Table If Exists #Data
Create Table #Data(string VarChar(100))

Insert Into #Data(string)
Values('Customer (A12445)'),('Mi(rosoft (A122)'),('1234 (Asdf) (2234)'),('A, Inc (334562)')

;With Rev As
(Select string, Reverse(string) As rs From #Data)
Select
string,
Reverse(Substring(rs, CharIndex(')', rs) + 1, CharIndex('(', rs) - CharIndex(')', rs) - 1))
From Rev
3

With no requirement for an (, but allowing it to be used over a number without a ( in front of it if both exist:

Drop Table If Exists #Data
Create Table #Data(string varchar(100))
Truncate table #data
Insert Into #Data(string)
Values('Customer (A12445)'),('Mi(rosoft (A122)'),('1234 (Asdf) (2234)'),('A, Inc (334562)'),
    ('IBM 111'),('Unknown id')

SELECT string, SUBSTRING(string, calc2.start_of_id, calc3.length_of_id) AS id
FROM #Data
CROSS APPLY (
    SELECT PATINDEX('%([0-9]%', string) + 1 AS start_of_id
) AS calc1
CROSS APPLY (
    SELECT CASE WHEN start_of_id = 1 THEN PATINDEX('%[0-9]%', string) ELSE start_of_id END 
        AS start_of_id    
) AS calc2
CROSS APPLY (
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(string + '.', calc2.start_of_id, 100)) - 1 AS length_of_id
) AS calc3