CODEWITHSUNDEEP
sqlsplit
user1086159
user1086159

Reputation: 1055

Splitting numbers and letters in SQL Server 2005 table

I have a column that contains values similar to:

034004         
034010         
06012AB        
06012C         
06012D         
06012P               
06026C         
06026P

Is there any way to separate or split these in two separate columns as numbers and letters? Does it matter that not all numbers contain letters?

I am using SQL Server Management Studio 2005

Cheers in advance for anything that can point me in the right direct

Upvotes: 2

Views: 5138

Answers (1)

Martin Smith
Martin Smith

Reputation: 453908

Assuming it is always one group of numbers then (possibly) one group of letters

SELECT SUBSTRING(YourCol, 0, P),
       SUBSTRING(YourCol, P, 8000)
FROM   YourTable
       CROSS APPLY(SELECT PATINDEX('%[^0-9]%', YourCol + 'A')) Split(P)

Upvotes: 5

Related Questions