How to split string in sql?

Question

I have two tables, in one I have data like this:

id  description
2   12.07.13y 1000eur to bank account KZ21321o0002134   
4   To bank account KZasd9093636 12 of May 2016y 200dusd

And I have a second table where I need to put filtered information from table first like:

id
data
bank_account
tranfered_money

First i need to split description,then i need to recognize ban_account which always started with "KZ",data and transfered_money

What RDBMS do you use? In ORACLE (10g at least), you can use regular expressions such as REGEXP_SUBSTR and try to split the string with a fitting pattern. — Demo, Oct 11 '16 at 10:56
How many records have you got? If below 1000 or so, then manually re-entering all data takes less time :) — Arvo, Oct 11 '16 at 11:09
http://stackoverflow.com/questions/10914576/t-sql-split-string — Dexion, Oct 11 '16 at 11:21
You may just hard-code all 3 cases . Otherwise you need full regexp capability, which can be integrated into TSql via CLR. See http://www.codeproject.com/Articles/85954/Use-RegEx-in-SQL-with-CLR-Procs or third party product (free version will do) http://www.sqlsharp.com/features/ — Serg, Oct 11 '16 at 11:23
Hey! Just curious, you solved your problem? Have you tried my solution? — gofr1, Oct 19 '16 at 06:55

score 0 · Answer 1 · answered Oct 11 '16 at 11:49

This is just awful but seems to be able to extract the ban_acount:

CREATE TABLE exp
(
 column1 varchar(400)
);

Insert into exp (column1) values ('12.07.13y 1000eur to bank account KZ21321o0002134');
Insert into exp (column1) values ('To bank account KZasd9093636 12 of May 2016y 200dusd');

Select
CASE
  WHEN CHARINDEX ( SPACE(1), SUBSTRING ( column1, CHARINDEX('KZ' , column1),LEN(column1))) = 0
  THEN SUBSTRING ( column1, CHARINDEX('KZ' , column1),LEN(column1))
  ELSE SUBSTRING ( SUBSTRING (column1, CHARINDEX('KZ' , column1),LEN(column1)), 0, CHARINDEX (SPACE(1), SUBSTRING(column1, CHARINDEX('KZ' , column1),LEN(column1))))
END result
From exp

gofr1 · Answer 2 · 2016-10-11T12:01:57.387

At first convert your table to XML.

Then create table with month/weekdays names and digits from 1 to 3000 (or you can take 2016 as current year)

You will need a table with currency. I made one based on data from here.

DECLARE @x xml

;WITH YourTable AS (  --I use this CTE, you should use your table in scripts below
SELECT *
FROM (VALUES
(2,   '12.07.13y 1000eur to bank account KZ21321o0002134'),
(4,   'To bank account KZasd9093636 12 of May 2016y 200dusd')
) as t(id, [description])
)

SELECT @x = (  --XML sample that we get you can see below after output
    SELECT CAST(N'<row id="'+CAST(id as nvarchar(max))+'"><b>'+REPLACE([description],' ','</b><b>')+'</b></row>' as xml)
    FROM YourTable
    FOR XML PATH('')
)
;WITH CurrencyList AS ( --Currency table
SELECT *
FROM (VALUES
('AED', 'United Arab Emirates Dirham'),
('AFN', 'Afghanistan Afghani'),
('ALL', 'Albania Lek'),
('AMD', 'Armenia Dram'),
...
('ZAR', 'South Africa Rand'),
('ZMW', 'Zambia Kwacha'),
('ZWD', 'Zimbabwe Dollar')
) as t(code, countryname)
),cte AS (  --generate numbers 1 to 3000
SELECT 0 as d
UNION ALL
SELECT d+1
FROM cte 
WHERE d < 3000
), datenames AS ( --generate datenames
SELECT  d,
        CASE WHEN d < 7 THEN DATENAME(weekday,DATEADD(day,d,'1970-01-01 00:00:00.000')) ELSE NULL END as weekday_name,
        CASE WHEN d < 12 THEN DATENAME(month,DATEADD(month,d,'1970-01-01 00:00:00.000')) ELSE NULL END as mon_name
FROM cte
)

--Final query
SELECT  t.c.value('../@id','int') as id,
        t.c.value('.','nvarchar(max)') as str_part,
        CASE WHEN t.c.value('.','nvarchar(max)') LIKE 'KZ%' THEN 'bank_account' 
                WHEN countryname IS NOT NULL THEN 'tranfered_money'
                WHEN dn.d IS NOT NULL OR RIGHT(t.c.value('.','nvarchar(max)'),1) ='y' THEN 'datepart'
                ELSE NULL END as what_is
FROM @x.nodes('/row/b') as t(c)
LEFT JOIN CurrencyList cl
    ON RIGHT(t.c.value('.','nvarchar(max)'),3) = cl.code --check 3 last symbols of string with currency codes
LEFT JOIN datenames dn
    ON  dn.d = t.c.value('. cast as xs:int?','int')  -- if it is a day/month/year number
        OR t.c.value('.','nvarchar(max)') = dn.weekday_name -- or it is a week day name
        OR t.c.value('.','nvarchar(max)') = dn.mon_name --or month name
OPTION (MAXRECURSION 0)

Will bring you:

id  str_part        what_is
2   12.07.13y       datepart
2   1000eur         tranfered_money
2   to              NULL
2   bank            NULL
2   account         NULL
2   KZ21321o0002134 bank_account
4   To              NULL
4   bank            NULL
4   account         NULL
4   KZasd9093636    bank_account
4   12              datepart
4   of              NULL
4   May             datepart
4   2016y           datepart
4   200dusd         tranfered_money

After that you need to bring dates in normal date form and that is all.

XML Sample:

<row id="2">
  <b>12.07.13y</b>
  <b>1000eur</b>
  <b>to</b>
  <b>bank</b>
  <b>account</b>
  <b>KZ21321o0002134</b>
</row>
<row id="4">
  <b>To</b>
  <b>bank</b>
  <b>account</b>
  <b>KZasd9093636</b>
  <b>12</b>
  <b>of</b>
  <b>May</b>
  <b>2016y</b>
  <b>200dusd</b>
</row>

How to split string in sql?

2 Answers2