Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Split out in columns from 1 field in SQL2012

Posted on 2015-01-07
8
Medium Priority
?
185 Views
Last Modified: 2015-02-11
I have the following data in a field called details and I want to split this out into the following columns from one command

£ 68.25 (£ 68.25) to SI 38816
£ 360.00 (£ 360.00) to SI 26231
£ 360.00 (£ 360.00) to SI 38151
£ 165.72 (€ 210.00) to SI 38814

Col1 £  (ie 1st letter)
col2 value after 1st letter and before the (
Col3 1st symbol after the first (
Col4 the value enclosed by the closing bracket)
Col5 the first word after 0 to ie SI
Col6 the last value (this is actually the most important but I've noticed this could be a string in a very few cases

how would I split this into a temp table?
0
Comment
Question by:Chris Michalczuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40535959
Here's a way...

declare @t1 table (mult varchar(500))
insert into @t1 (mult) values ('£ 68.25 (£ 68.25) to SI 38816'),
('£ 360.00 (£ 360.00) to SI 26231'),
('£ 360.00 (£ 360.00) to SI 38151'),
('£ 165.72 (€ 210.00) to SI 38814')

select  
SUBSTRING(mult,1,1) as Col1
,ltrim(rtrim(SUBSTRING(mult,2,charindex('(',mult)-2))) as Col2
,SUBSTRING(mult,charindex('(',mult)+1,1) as Col3
,SUBSTRING(mult,charindex('(',mult)+2,CHARINDEX(')',mult)-charindex('(',mult)-2) as Col4
,SUBSTRING(mult,CHARINDEX(') to ',mult)+5,2) as Col5
,RIGHT(mult, CHARINDEX(' ', REVERSE(mult)) - 1)as Col6
from @t1

Open in new window


You'd just use the select statement, replacing 'mult' with the name of your field.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40536111
/*
--run one time to set up test data
CREATE TABLE #test (
    details varchar(100)
    )
insert into #test ( details )
select '£ 68.25 (£ 68.25) to SI 38816' union all
select '£ 360.00 (£ 360.00) to SI 26231' union all
select '£ 360.00 (£ 360.00) to SI 38151' union all
select '£ 165.72 (€ 210.00) to SI 38814' union all
select '£ 360.00 (£ 360.00) to SIG 381517' --test value longer than 2 bytes after " to", in case it's possible

*/

SELECT
    LEFT(details, 1) AS Value1,
    LTRIM(RTRIM(SUBSTRING(details, 2, pos_of_left_paren - 2))) AS Value2,
    SUBSTRING(details, pos_of_left_paren + 1, 1) AS Value3,
    LTRIM(SUBSTRING(details, pos_of_left_paren + 2, pos_of_right_paren - pos_of_left_paren - 2)) AS Value4,
    SUBSTRING(details, pos_of_space_to + 4, CHARINDEX(' ', details, pos_of_space_to + 4) - (pos_of_space_to + 4)) AS Value5,
    RIGHT(details, CHARINDEX(' ', REVERSE(details)) - 1) AS Value6
FROM #test t
CROSS APPLY (
    SELECT CHARINDEX('(', details) AS pos_of_left_paren,
        CHARINDEX(')', details) AS pos_of_right_paren,
        CHARINDEX(' to', details) AS pos_of_space_to
) AS assign_alias_names
0
 
LVL 18

Expert Comment

by:Simon
ID: 40536157
@Scott: Much better! I knew I should have gone the extra mile and dealt with the possibility of longer values in column 5!
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Accepted Solution

by:
Chris Michalczuk earned 0 total points
ID: 40537473
@Scott:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

SELECT
    LTRIM(RTRIM(SUBSTRING(details, 2, pos_of_left_paren - 2))) AS Value2
      FROM CPMAuditUsage
      CROSS APPLY (
    SELECT CHARINDEX('(', details) AS pos_of_left_paren,
        CHARINDEX(')', details) AS pos_of_right_paren,
        CHARINDEX(' to', details) AS pos_of_space_to
) AS assign_alias_names
0
 
LVL 18

Expert Comment

by:Simon
ID: 40537532
Does every row of the data you're passing to the select statement look like the sample you posted in your question?

I'd guess that you have a line without at least one of the key identifying strings in it, or not in the position suggested by your sample data  -
'('          - cannot be the first character of the string
')'
' to'

You might want to add a where clause to the end of the SELECT statement to avoid trying to process rows with strings that don't conform to the expected pattern.
    CROSS APPLY (
SELECT CHARINDEX('(', details) AS pos_of_left_paren,
        CHARINDEX(')', details) AS pos_of_right_paren,
        CHARINDEX(' to', details) AS pos_of_space_to
) AS assign_alias_names 
WHERE details like '_%(_%) to% _%[^ ]'

Open in new window


You can use a separate query
SELECT * from CPMAuditUsage where details NOT LIKE '_%(_%) to% _%[^ ]'

Open in new window

To see which rows do not suit the current solution.

Be aware that with both Scott's proposed solution and my own, trailing spaces in your string currently prevent Col6 from being populated correctly. If your column is nvarchar, the LIKE pattern will omit those, but if it is char or varchar they would be included but have empty string for col6 value.

That can obviously be fixed by adding a RTRIM to this line.
  RIGHT(details, CHARINDEX(' ', REVERSE(rtrim(details))) - 1) AS Value6

Open in new window

but the point is that if your data isn't properly represented by the sample you included, the solution isn't likely to work entirely as expected.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40538122
Sounds like some data doesn't have a (.

Can you list sample data in other formats and briefly state/show how they should be interpreted.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40593915
Good luck with future qs.
0
 

Author Closing Comment

by:Chris Michalczuk
ID: 40602749
this only parttially solved it and I think I found the answer in an earlier question to my issue
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

670 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