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


Solution:1

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.


Solution:2

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.


Solution:3

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


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

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