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

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)


and help is appreciated.

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)
Reverse(Substring(rs, CharIndex(')', rs) + 1, CharIndex('(', rs) - CharIndex(')', rs) - 1))
From Rev

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
    SELECT PATINDEX('%([0-9]%', string) + 1 AS start_of_id
) AS calc1
    SELECT CASE WHEN start_of_id = 1 THEN PATINDEX('%[0-9]%', string) ELSE start_of_id END 
        AS start_of_id    
) AS calc2
    SELECT PATINDEX('%[^0-9]%', SUBSTRING(string + '.', calc2.start_of_id, 100)) - 1 AS length_of_id
) AS calc3