Tutorial :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


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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »