How to split numbers and string into two columns using TSQL

Question

In my project, I have a column that contains numbers (including "-" symbol) and a string. I want to split it into two columns. The separator between numbers and string differs it can be " " or " - ". Is it possible to solve this issue by means of a TSQL query? This TSQL engine is placed in Devexpress WinForms designer.

Example:

Col:
343234-2321 String string
402-09-12 - Another string
Just string
303-404 - Text field

Expected result

Col1	Col2
343234-2321	String string
402-09-12	Another string
NULL	Just string
303-404	Text field

Thank you in advance!

Sample data, and expected results, will help us help you. Don't forget your attempts as well! — Thom A
– Thom A ♦, Commented Jun 22, 2021 at 13:25
What the output you expect for String1 string2 99283948 string3? — astentx
– astentx, Commented Jun 22, 2021 at 14:05

Stu · Accepted Answer · 2021-06-22 17:57:08Z

1

Assuming you always need to break your string in half after the end of the numeric digits as your sample data demonstrates, a possible solution is to use patindex:

with s as (
    select col, PatIndex('%[A-z]%',col) d
    from t
)
select col,
    NullIf(Trim(case when Substring(Left(col,d-1),d-2,1)='-' then 
      Left(col,d-3)
      else Left(col,d-1) end),'') Col1,
    NullIf(Trim(NullIf(Substring(col,d,Len(col)),'')),'') Col2
from s

See Example DB Fiddle

Note, if you are using SQL2016 or prior you'll need to replace trim with nested ltrim & rtrim

answered Jun 22, 2021 at 17:57

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to split numbers and string into two columns using TSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related