Split alpha-numeric string into multiple columns in SQL Server

To break the string into numerous columns, a certain pattern should be used. Splitting a string can be done using various delimiters such as a comma, semicolon, or tab, or it can be split using the amount of characters provided as input.

Here’s an example of combining a username and a phone number into a string. Our requirement is to divide them into two columns.

CREATE TABLE #Temp(StringData VARCHAR(100));

SELECT ‘Smith9856787901
UNION SELECT ‘Will9856787890
UNION SELECT ‘Ron9856787891
UNION SELECT ‘Roberto9856787899
UNION SELECT ‘Fernandes9856787990
UNION SELECT ‘Alicia9856789990‘;

If you’ve observed, the names aren’t all the same length, but the phone numbers are. As a result, we will concentrate on the phone number.

Here is our code to split them.

SELECT REPLACE(StringData, RIGHT(StringData, 10),”),
RIGHT(StringData, 10) FROM #Temp

Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s