• Status: Solved
• Priority: Medium
• Security: Public
• Views: 206

# Split out in columns from 1 field in SQL2012

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
Chris Michalczuk
• 3
• 3
• 2
1 Solution

Commented:
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
``````

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

Senior DBACommented:
/*
--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

Commented:
@Scott: Much better! I knew I should have gone the extra mile and dealt with the possibility of longer values in column 5!
0

ConsultantAuthor Commented:
@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

Commented:
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% _%[^ ]'
``````

You can use a separate query
``````SELECT * from CPMAuditUsage where details NOT LIKE '_%(_%) to% _%[^ ]'
``````
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
``````
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

Senior DBACommented:
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

Senior DBACommented:
Good luck with future qs.
0

ConsultantAuthor Commented:
this only parttially solved it and I think I found the answer in an earlier question to my issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.