Solved

SQL statement split a data from one field into 4 different fields

Posted on 2014-09-06
18
253 Views
Last Modified: 2014-09-08
I have a field in a database called DETAILS.
this holds  5 bits of information that I would like to split into 5 separate columns into a database

Question is what statements would I use to deal with this and this data is consistent

for example

£ 282.11 ($ 476.00) from SR 32424

field 1 = £ 282.11  this is the first work after £ at start of the string
field 2 =  $ 476.00  this is anything between the ()
field 3 - from  this is the first word after the )
field 4 = SR  this is the 2nd to last part of the string
field 5 = 32424  this is the last part of the string


£ 26.50 ($ 0.00) to SI 11186

field 1 = £ 26.50  this is the first work after £ at start of the string
field 2 =  $ 0.00 this is anything between the ()
field 3 - to  this is the first word after the )
field 4 =  SI  this is the 2nd to last part of the string
field 5 = 11186  this is the last part of the string

example

673.90 Link Removed

this would have the first bit 673.90 in the first field1 and "Link Removed" in field3

what statements would I use to deal with this and this data is consistent


The data is as follows


£ 282.11 ($ 476.00) from SR 32424
£ 21.93 ($ 0.00) from SC 32446
£ 177.80 ($ 300.00) from SR 32424
£ 13.82 ($ 0.00) from SC 32447
£ 4148.64 ($ 7000.00) from SR 32424
£ 322.49 ($ 0.00) from SC 32448
£ 343.74 ($ 580.00) from SR 32424
£ 26.72 ($ 0.00) from SC 32449
£ 561.84 ($ 948.00) from SR 32424
£ 43.68 ($ 0.00) from SC 32450


£ 26.50 ($ 0.00) to SI 11186
£ 1412.70 ($ 2250.00) to SI 437

673.90 Link Removed
0
Comment
Question by:chrismichalczuk
18 Comments
 
LVL 2

Expert Comment

by:DanielT
ID: 40307698
Unsure of whether SQL can do this without coding and/or dependent on the 'flavor' of SQL. Someone else may be better to answer that, directly.

But - just a question... may not apply.
Since this is going to change tables and require other related changes in your database, queries/views etc; could you export the data and use Excel to parse the data into the columns required? Then import that data back with the separated column data. How you would reintegrate would depend of whether you are creating a new table and referencing through a foreign key or wanting to split the fields into the same table. No matter which - be sure you plan so you maintain your data integrity.
0
 
LVL 1

Expert Comment

by:Subramani N
ID: 40307755
I just tried doing it for the first scenario. As you per your statement the data is consistent. So I tried doing it with string operations. See if it works. Convert it as a function and use it.

Execute this as it is and see.
--splitting the column by Single Space.
DECLARE @xml xml,@str varchar(100),@delimiter varchar(10)
SET @str= '£ 282.11 ($ 476.00) from SR 32424'
SET @delimiter =' '
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT SEQ_NO = IDENTITY(INT,1,1), C.value('.', 'varchar(10)') as value INTO #TMP FROM @xml.nodes('X') as X(C)
SELECT * FROM #TMP

CREATE TABLE #TMP_DATA(DAT1 CHAR(10), DAT2 CHAR(10), DAT3 CHAR(10), DAT4 CHAR(10), DAT5 CHAR(10), DAT6 CHAR(10), DAT7 CHAR(10))
INSERT INTO #TMP_DATA SELECT '','','','','','',''
UPDATE #TMP_DATA SET DAT1 = (SELECT value FROM #TMP WHERE SEQ_NO = 1)
UPDATE #TMP_DATA SET DAT2 = value FROM #TMP WHERE SEQ_NO = 2
UPDATE #TMP_DATA SET DAT3 = value FROM #TMP WHERE SEQ_NO = 3
UPDATE #TMP_DATA SET DAT4 = value FROM #TMP WHERE SEQ_NO = 4
UPDATE #TMP_DATA SET DAT5 = value FROM #TMP WHERE SEQ_NO = 5
UPDATE #TMP_DATA SET DAT6 = value FROM #TMP WHERE SEQ_NO = 6
UPDATE #TMP_DATA SET DAT7 = value FROM #TMP WHERE SEQ_NO = 7
SELECT * FROM #TMP_DATA

--MYTABLE - destination table
CREATE TABLE MYTABLE(FIELD1 CHAR(100), FIELD2 CHAR(100), FIELD3 CHAR(100), FIELD4 CHAR(100), FIELD5 CHAR(100))
INSERT INTO MYTABLE SELECT '','','','',''
UPDATE
	MYTABLE
SET
	FIELD1 = RTRIM(DAT1) + ' ' + RTRIM(DAT2),
	FIELD2 = SUBSTRING(RTRIM(DAT3),2,2) + ' ' + SUBSTRING(RTRIM(DAT4),1,LEN(DAT4)-1),
	FIELD3 = RTRIM(DAT5),
	FIELD4 = RTRIM(DAT6),
	FIELD5 = RTRIM(DAT7)
FROM
	#TMP_DATA
SELECT * FROM MYTABLE

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40308219
Which version of sql server is this please? (I presume it is only one version, but you have 2005 & 2008 as topics. )
0
 

Author Comment

by:chrismichalczuk
ID: 40308245
I have SQL 2012
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40308248
|    FIELD1 |    FIELD2 |       FIELD3 | FIELD4 | FIELD5 |                             DETAILS |
|-----------|-----------|--------------|--------|--------|-------------------------------------|
|  £ 282.11 |  $ 476.00 |        from  |     SR |  32424 |   £ 282.11 ($ 476.00) from SR 32424 |
|   £ 21.93 |    $ 0.00 |        from  |     SC |  32446 |      £ 21.93 ($ 0.00) from SC 32446 |
|  £ 177.80 |  $ 300.00 |        from  |     SR |  32424 |   £ 177.80 ($ 300.00) from SR 32424 |
|   £ 13.82 |    $ 0.00 |        from  |     SC |  32447 |      £ 13.82 ($ 0.00) from SC 32447 |
| £ 4148.64 | $ 7000.00 |        from  |     SR |  32424 | £ 4148.64 ($ 7000.00) from SR 32424 |
|  £ 322.49 |    $ 0.00 |        from  |     SC |  32448 |     £ 322.49 ($ 0.00) from SC 32448 |
|  £ 343.74 |  $ 580.00 |        from  |     SR |  32424 |   £ 343.74 ($ 580.00) from SR 32424 |
|   £ 26.72 |    $ 0.00 |        from  |     SC |  32449 |      £ 26.72 ($ 0.00) from SC 32449 |
|  £ 561.84 |  $ 948.00 |        from  |     SR |  32424 |   £ 561.84 ($ 948.00) from SR 32424 |
|   £ 43.68 |    $ 0.00 |        from  |     SC |  32450 |      £ 43.68 ($ 0.00) from SC 32450 |
|   £ 26.50 |    $ 0.00 |          to  |     SI |  11186 |        £ 26.50 ($ 0.00) to SI 11186 |
| £ 1412.70 | $ 2250.00 |          to  |     SI |    437 |     £ 1412.70 ($ 2250.00) to SI 437 |
|    673.90 |    (null) | Link Removed | (null) | (null) |                 673.90 Link Removed |

Open in new window

Produced by the following query:
select
        field1
      , field2
      , field3
      , field4
      , field5
      , details
from theTable
cross apply (
             select case when left(details,2) = '£ ' then 1 else 0 
                    end
                  , len(details)
            ) ca1 (is5part, lng)
cross apply (
             select case when is5part = 1 then
                         left(details,charindex(' ',substring(details,3,lng)) + 1)
                    else
                         left(details,charindex(' ', details) - 1)
                    end
                  , charindex('(',details)
                  , charindex(')',details)
                  , substring(details,charindex(')',details)+2,lng)
            ) ca2 (field1, p1, p2, f3to5)
cross apply (
             select case when is5part = 1 then
                        substring(details,p1+1, (p2-p1) - 1)
                    end
                  , case when is5part = 1 then
                         left(f3to5,charindex(' ',f3to5))
                    else
                         substring(details,charindex(' ', details)+1,lng)
                    end
                  , case when is5part = 1 then
                         substring(f3to5,charindex(' ',f3to5,2)+1,2)
                    end
                  , case when is5part = 1 then
                         right(f3to5,charindex(' ',reverse(f3to5))-1)
                    end
            ) ca3 (field2, field3,field4, field5)
;

-- more info:
    CREATE TABLE TheTable
    	([Details] varchar(35))
    ;
    	
    INSERT INTO TheTable
    	([Details])
    VALUES
    	('£ 282.11 ($ 476.00) from SR 32424'),
    	('£ 21.93 ($ 0.00) from SC 32446'),
    	('£ 177.80 ($ 300.00) from SR 32424'),
    	('£ 13.82 ($ 0.00) from SC 32447'),
    	('£ 4148.64 ($ 7000.00) from SR 32424'),
    	('£ 322.49 ($ 0.00) from SC 32448'),
    	('£ 343.74 ($ 580.00) from SR 32424'),
    	('£ 26.72 ($ 0.00) from SC 32449'),
    	('£ 561.84 ($ 948.00) from SR 32424'),
    	('£ 43.68 ($ 0.00) from SC 32450'),
    	('£ 26.50 ($ 0.00) to SI 11186'),
    	('£ 1412.70 ($ 2250.00) to SI 437'),
    	('673.90 Link Removed')
    ;

  [1]: http://sqlfiddle.com/#!3/47457/1

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40308250
re: 2012
Thanks, turns out it wasn't necessary here, but often version is important.
2012 in particular added several new features.
0
 

Author Comment

by:chrismichalczuk
ID: 40308295
Paul I want to create the temp table for all splits based on the field details from audit.usage. I will also want to take other fields too. How do I do this in your code as it seems to almost do what I want.
I assume I need to create a table with the fields

so is it create mytable with all the above fields

and then how does the insert work without the values being as you have them?
select
        field1
      , field2
      , field3
      , field4
      , field5
      , details
      , tran_number
from [dbo].[AUDIT_USAGE]
where type = 'SR'

so is it create mytable with all the above fields

and then how does the insert work without the values being as you have them?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40308299
You can simply use INTO

select ...
INTO [give-it-a-name-here] -- creates the new table with the selected fields
from ...
where ...
0
 

Author Comment

by:chrismichalczuk
ID: 40308301
select
        field1  this is the split 1
      , field2
      , field3
      , field4
      , field5
      , details   this is the field to be interogated that will create field1 to 5 above
, trans_number  this is an actual field in audit_usage (I will want to take other fields too
from [dbo].[AUDIT_USAGE]

this failed using your solution Subramani N when I tried to point it at the specific table
The field is details and I want to create a separate table that creates the above fields

so the create table bit fails

and what would I put into the insert into MYTABLE

how are the values listed in the value statement ie

Values
([dbo].[AUDIT_USAGE].field1, [dbo].[AUDIT_USAGE].field2 etc)



also the declare

SET @str  ***** should this point at the field audit_usage.details which is what I want to extract the info from

DECLARE @xml xml,@str varchar(100),@delimiter varchar(10)
SET @str= '£ 282.11 ($ 476.00) from SR 32424'
SET @delimiter =' '
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT SEQ_NO = IDENTITY(INT,1,1), C.value('.', 'varchar(10)') as value INTO #TMP FROM @xml.nodes('X') as X(C)
SELECT * FROM #TMP
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:chrismichalczuk
ID: 40308304
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ')'.


select
        field1
      , field2
      , field3
      , field4
      , field5
      , details
from [dbo].[AUDIT_USAGE]
cross apply (
             select case when left(details,2) = '£ ' then 1 else 0
                    end
                  , len(details)
            ) ca1 (is5part, lng)
cross apply (
             select case when is5part = 1 then
                         left(details,charindex(' ',substring(details,3,lng)) + 1)
                    else
                         left(details,charindex(' ', details) - 1)
                    end
                  , charindex('(',details)
                  , charindex(')',details)
                  , substring(details,charindex(')',details)+2,lng)
            ) ca2 (field1, p1, p2, f3to5)
cross apply (
             select case when is5part = 1 then
                        substring(details,p1+1, (p2-p1) - 1)
                    end
                  , case when is5part = 1 then
                         left(f3to5,charindex(' ',f3to5))
                    else
                         substring(details,charindex(' ', details)+1,lng)
                    end
                  , case when is5part = 1 then
                         substring(f3to5,charindex(' ',f3to5,2)+1,2)
                    end
                  , case when is5part = 1 then
                         right(f3to5,charindex(' ',reverse(f3to5))-1)
                    end
            ) ca3 (field2, field3,field4, field5)
;

-- more info:
    CREATE TABLE TheTable
          (
            [Details] varchar(35)
            ,[Field1] varchar(35)
        ,[Field2] varchar(35)
        ,[Field3] varchar(35)
        ,[Field4] varchar(35)
        ,[Field5] varchar(35))
       ;
          
    INSERT INTO TheTable
          ([Details],field1,Field2,Field3,Field4,Field5)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40308307
1 select ...
2 INTO [give-it-a-name-here] -- creates the new table with the selected fields
3 from ...
4 where ...

You cannot put the INTO statement and the end, there is a specific sequence you must adhere to

and it isn't "insert into", just "into", if placed in the correct place
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40308449
mmm, maybe you just didn't realize that the -- more info isn't needed.

select
        field1
      , field2
      , field3
      , field4
      , field5
      , details

INTO MYTABLE --<< this is all you need to send the query to a new table called MYTABLE

from [dbo].[AUDIT_USAGE]
cross apply (
             select case when left(details,2) = '£ ' then 1 else 0 
                    end
                  , len(details)
            ) ca1 (is5part, lng)
cross apply (
             select case when is5part = 1 then
                         left(details,charindex(' ',substring(details,3,lng)) + 1)
                    else
                         left(details,charindex(' ', details) - 1)
                    end
                  , charindex('(',details)
                  , charindex(')',details)
                  , substring(details,charindex(')',details)+2,lng)
            ) ca2 (field1, p1, p2, f3to5)
cross apply (
             select case when is5part = 1 then
                        substring(details,p1+1, (p2-p1) - 1)
                    end
                  , case when is5part = 1 then
                         left(f3to5,charindex(' ',f3to5))
                    else
                         substring(details,charindex(' ', details)+1,lng)
                    end
                  , case when is5part = 1 then
                         substring(f3to5,charindex(' ',f3to5,2)+1,2)
                    end
                  , case when is5part = 1 then
                         right(f3to5,charindex(' ',reverse(f3to5))-1)
                    end
            ) ca3 (field2, field3,field4, field5)
; -- the query ends here, you don't need the "more info" I supplied earlier

Open in new window

0
 

Author Comment

by:chrismichalczuk
ID: 40309270
Paul this is great and nearly sorts it out now. However the records below dont follow the pattern above.
In this query I always want the last 2 words in the sentence in field 5 (the last one) this relates to an invoice or credit number and the 2 digits just before it relate to a type code in the DB (SI, SR etc)

I can use a where clause to get anything beginning with £ abd this works find however I also need to take care of the lines that don't start with £


field1      field2      field3      field4      field5      details
5.72      NULL      Link Removed      NULL      NULL      5.72 Link Removed     this is fine
317.28      NULL      from SR 515      NULL      NULL      317.28 from SR 515     (want SR in field 4 , 515 in field 5  from in field 3)
317.28      NULL      from SR 515      NULL      NULL      317.28 from SR 515
317.28      NULL      from SR 515      NULL      NULL      317.28 from SR 515
317.28      NULL      from SR 515      NULL      NULL      317.28 from SR 515
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40309311
As you see in my original post, I had all 5 fields working from the sample data. That was based on the data in the question, wich resulted in these inserts - please study these carefully
    INSERT INTO TheTable
    	([Details])
    VALUES
    	('£ 282.11 ($ 476.00) from SR 32424'),
    	('£ 21.93 ($ 0.00) from SC 32446'),
    	('£ 177.80 ($ 300.00) from SR 32424'),
    	('£ 13.82 ($ 0.00) from SC 32447'),
    	('£ 4148.64 ($ 7000.00) from SR 32424'),
    	('£ 322.49 ($ 0.00) from SC 32448'),
    	('£ 343.74 ($ 580.00) from SR 32424'),
    	('£ 26.72 ($ 0.00) from SC 32449'),
    	('£ 561.84 ($ 948.00) from SR 32424'),
    	('£ 43.68 ($ 0.00) from SC 32450'),
    	('£ 26.50 ($ 0.00) to SI 11186'),
    	('£ 1412.70 ($ 2250.00) to SI 437'),
    	('673.90 Link Removed')
    ;

  [1]: http://sqlfiddle.com/#!3/47457/1

Open in new window

If you visit http://sqlfiddle.com/#!3/47457/1 you will see it operating live.

If that data is NOT accurately representing your data please supply inserts that are accurate.

Then:
It should not be necessary to use a where filter for anything starting with £
What happens if you do not do this?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40309316
You will have to drop [MYTABLE] before re-running that query. Using INTO requires that the table does not exist.

Did you read the reference on INTO? I strongly suggest you do.

If you decide yo do not like using INTO then you have to create the table and form insert statements suitable to that table;  this I would leave up to you (or you ask another question). I am not offering that.
0
 

Author Comment

by:chrismichalczuk
ID: 40309336
thanks paul you've been a great help. Once last question now to close this thread.

what would the syntax be to take the LAST work from the field into a new one
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40309360
>>"what would the syntax be to take the LAST work from the field into a new one"

I don't understand.
Could you demonstrate what you are saying by example?
0
 

Author Closing Comment

by:chrismichalczuk
ID: 40309488
Paul's answer has been a great help for spliting data into segments. Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now