CODEWITHSUNDEEP
sqlsplit
dfm
dfm

Reputation: 31

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 Numbers and Letters.

Thanks in advance

Upvotes: 3

Views: 633

Answers (4)

APC
APC

Reputation: 146219

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.

Upvotes: 3

DNNX
DNNX

Reputation: 6255

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

Upvotes: 0

Seth Petry-Johnson
Seth Petry-Johnson

Reputation: 12085

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

Upvotes: 0

No Refunds No Returns
No Refunds No Returns

Reputation: 8336

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.

Upvotes: 0

Related Questions