Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

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
Chris Michalczuk
Asked:
Chris Michalczuk
1 Solution
 
DanielTCommented:
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
 
Subramani NCommented:
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
 
PortletPaulCommented:
Which version of sql server is this please? (I presume it is only one version, but you have 2005 & 2008 as topics. )
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Chris MichalczukConsultantAuthor Commented:
I have SQL 2012
0
 
PortletPaulCommented:
|    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
 
PortletPaulCommented:
re: 2012
Thanks, turns out it wasn't necessary here, but often version is important.
2012 in particular added several new features.
0
 
Chris MichalczukConsultantAuthor Commented:
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
 
PortletPaulCommented:
You can simply use INTO

select ...
INTO [give-it-a-name-here] -- creates the new table with the selected fields
from ...
where ...
0
 
Chris MichalczukConsultantAuthor Commented:
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
 
Chris MichalczukConsultantAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
Chris MichalczukConsultantAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
Chris MichalczukConsultantAuthor Commented:
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
 
PortletPaulCommented:
>>"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
 
Chris MichalczukConsultantAuthor Commented:
Paul's answer has been a great help for spliting data into segments. Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now