SQL Split like Select

Question

I have a field that contains values like:

A12345
AB456
1234
AA 45

Is there anyway to Select these in two separate columns as Numbers and Letters.

Thanks in advance

Answer 1

If you're using a SQL Engine that support user-defined functions you can write on to parse it out and return a table of unique values. If you're going to do this a lot though, you'd probably be better served storing them as separate fields so you can manipulate them with DML instead of custom code.

Answer 2

If you're using SQL Server 2005, you can call into .NET code (such as C# or VB.NET regular expression features) via the CLR integration. Here's one article to get you started, I'm sure Google will turn up lots more: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Answer 3

create table tbl(data varchar(200))

insert into tbl(data)
select 'A12345' data union all
select 'AB456' union all
select '1234' union all
select 'AA 45'

-------------

select LEFT(data, PATINDEX('%[0-9]%', data)-1) as Letters,
       CAST(SUBSTRING(data, PATINDEX('%[0-9]%', data), 10000) AS INT) as Numbers
from tbl

Answer 4

If you don't have regex then perhaps something like this will cut it for you.

SQL> with t as ( select 'A12345' as str from dual
  2      union all
  3      select 'AB456' as str from dual
  4      union all
  5      select '1234' as str from dual
  6      union all
  7      select 'AA 45' as str from dual)
  8  select str
  9         , replace(translate(str, '0123456789'
 10                                , '          '), ' ', null) as AAA
 11         , replace(translate(str, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 12                                , '                          '), ' ', null) as NNN
 13  from t
 14  /

STR    AAA    NNN
------ ------ ------
A12345 A      12345
AB456  AB     456
1234          1234
AA 45  AA     45

SQL>

The translate() function converts numbers (or letters) into spaces, then the replace() turns spaces into NULLs.