SQL Split like Select

I have a field that contains values like:

A12345
AB456
1234
AA 45

Is there anyway to Select these in two separate columns as 开发者_如何学PythonNumbers and Letters.

Thanks in advance

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.

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.

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

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