SQL Split, How to Choose

Question

I have a field containing values ​​such as:

A12345
AB456
1234
AA 45

Is there anyway to select them in two separate columns in the form of numbers and letters.

Thanks in advance

Answer 1

If you don't have a regex, 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 function translate()converts numbers (or letters) to spaces, then replace()turns spaces to NULL.

Answer 2

SQL Engine, , . , , , , DML .

Answer 3

If you are using SQL Server 2005, you can call .NET code (for example, C # or VB.NET regular expression functions) using the CLR integration. Here is one article to get you started, I'm sure there will be more Google: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Answer 4

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