January 9, 2017 at 6:48 am
Hi,
I need to remove the number and semicolon and # and just get the text and insert as two rows .
CREATE TABLE #Split
(
ID INT,
Texts NVARCHAR(100)
)
INSERT INTO #Split (ID,TExts)
SELECT 1,'136;#BRZ Brazil' UNION
SELECT 2,'135;#BRZ Brazil;#260;#VEN Venezuela'
SELECT * FROM #Split
--Desired results
SELECT 1 AS ID,'BRZ Brazil' AS Texts UNION
SELECT 2 as ID,'BRZ Brazil' AS Texts UNION
SELECT 2 as ID,'VEN Venezuela' AS Texts
Please help.
Thanks,
PSB
January 9, 2017 at 7:11 am
PSB (1/9/2017)
Hi,I need to remove the number and semicolon and # and just get the text and insert as two rows .
CREATE TABLE #Split
(
ID INT,
Texts NVARCHAR(100)
)
INSERT INTO #Split (ID,TExts)
SELECT 1,'136;#BRZ Brazil' UNION
SELECT 2,'135;#BRZ Brazil;#260;#VEN Venezuela'
SELECT * FROM #Split
--Desired results
SELECT 1 AS ID,'BRZ Brazil' AS Texts UNION
SELECT 2 as ID,'BRZ Brazil' AS Texts UNION
SELECT 2 as ID,'VEN Venezuela' AS Texts
Please help.
Thanks,
PSB
Excellent job posting ddl, sample data and desired results. Makes this sort of thing so much easier to help.
You can leverage the DelimitedSplit8K function for this quite easily. You can find the code for that function by following the link in my signature about splitting strings. Once you have that function, and hopefully understand it, your query can be this simple.
SELECT s.ID
, x.Item as Texts
FROM #Split s
cross apply dbo.DelimitedSplit8K(REPLACE(Texts, '#', ''), ';') x
where x.ItemNumber % 2 = 0 --this eliminates the first value since you have groups of data.
order by s.ID
, x.ItemNumber
If at all possible you should stop storing multiple values in a single tuple like this. It violates 1NF and causes lots of pain. In your case you storing delimited groups of data which is even worse.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2017 at 7:28 am
What if there are more than one field where same split needs to be done ?
CREATE TABLE #Split
(
ID INT,
Texts NVARCHAR(100),Names nvarchar(100)
)
INSERT INTO #Split (ID,TExts,Names)
SELECT 1,'136;#BRZ Brazil','136;#Name3' UNION
SELECT 2,'135;#BRZ Brazil;#260;#VEN Venezuela','136;#Name1;#260;#Name2'
Thanks,
PSB
January 9, 2017 at 7:33 am
PSB (1/9/2017)
What if there are more than one field where same split needs to be done ?
CREATE TABLE #Split
(
ID INT,
Texts NVARCHAR(100),Names nvarchar(100)
)
INSERT INTO #Split (ID,TExts,Names)
SELECT 1,'136;#BRZ Brazil','136;#Name3' UNION
SELECT 2,'135;#BRZ Brazil;#260;#VEN Venezuela','136;#Name1;#260;#Name2'
Thanks,
PSB
Well this is a completely different setup. What would you expect as output from this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2017 at 7:49 am
SELECT 1 AS ID,'BRZ Brazil' AS Texts ,'Name3' AS Names UNION
SELECT 2 as ID,'BRZ Brazil' AS Texts ,'Name1' AS Names UNION
SELECT 2 as ID,'VEN Venezuela' AS Texts ,'Name2' AS Names
something like the above .
Thanks!
January 9, 2017 at 8:05 am
PSB (1/9/2017)
SELECT 1 AS ID,'BRZ Brazil' AS Texts ,'Name3' AS Names UNIONSELECT 2 as ID,'BRZ Brazil' AS Texts ,'Name1' AS Names UNION
SELECT 2 as ID,'VEN Venezuela' AS Texts ,'Name2' AS Names
something like the above .
Thanks!
Ouch!!! Is this seriously how you have your data in your tables? This is just awful. You have multiple columns of delimited data that relate to others ordinal position across columns. What a total nightmare to work with. What you would have to do is parse each column and include a row number, then you can join those two sets on row number.
Something along these lines.
with Texts as
(
SELECT s.ID
, x.Item as Texts
, ROW_NUMBER() over(order by s.ID, x.ItemNumber) as RowNum
FROM #Split s
cross apply dbo.DelimitedSplit8K(REPLACE(Texts, '#', ''), ';') x
where x.ItemNumber % 2 = 0 --this eliminates the first value since you have groups of data.
)
, Names as
(
SELECT s.ID
, x.Item as Names
, ROW_NUMBER() over(order by s.ID, x.ItemNumber) as RowNum
FROM #Split s
cross apply dbo.DelimitedSplit8K(REPLACE(Names, '#', ''), ';') x
where x.ItemNumber % 2 = 0 --this eliminates the first value since you have groups of data.
)
select t.ID
, t.Texts
, n.Names
from Texts t
join Names n on t.RowNum = n.RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2017 at 8:58 am
It's multiselect SharePoint data that is causing problem.
January 9, 2017 at 9:02 am
PSB (1/9/2017)
It's multiselect SharePoint data that is causing problem.
I feel your pain there. I have had to deal with that nightmare before too. Hopefully the solution I posted will let you figure out how to get the real data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2017 at 9:31 am
Thanks .I will try the solution posted by you shortly and would revert back .
January 9, 2017 at 10:33 am
Is there anyway I can add new columns TextID and NameID for the numeric fields for Text and Names field ?
--Something like the below resultset
SELECT 1 AS ID, 136 as TextID,'BRZ Brazil' AS Texts ,136 AS NameID,'Name3' AS Names UNION
SELECT 2 as ID, 135 as TextID,'BRZ Brazil' AS Texts ,136 as NameID,'Name1' AS Names UNION
SELECT 2 as ID, 260 AS TextID,'VEN Venezuela' AS Texts ,260 AS NameID,'Name2' AS Names
January 9, 2017 at 10:47 am
PSB (1/9/2017)
Is there anyway I can add new columns TextID and NameID for the numeric fields for Text and Names field ?--Something like the below resultset
SELECT 1 AS ID, 136 as TextID,'BRZ Brazil' AS Texts ,136 AS NameID,'Name3' AS Names UNION
SELECT 2 as ID, 135 as TextID,'BRZ Brazil' AS Texts ,136 as NameID,'Name1' AS Names UNION
SELECT 2 as ID, 260 AS TextID,'VEN Venezuela' AS Texts ,260 AS NameID,'Name2' AS Names
Any other requirements you haven't mentioned yet? This is yet another completely different animal.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2017 at 11:17 am
I'm impressed at your patience so far Sean, this is getting quite the ugly dataset.
As Sean said before, I think at this stage you would be better off redesigning your data. You don't want to rely on data in the format you're provided and continue to populate it in the same format. This is goes going to become a headache that you will regret.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2017 at 11:41 am
Sorry that's about it . No new requirement .
Thanks!
January 10, 2017 at 6:46 am
Still not able to figure out . 🙁
January 10, 2017 at 7:27 am
Is it really that difficult? There should be a better way to get this information, but I would need to get a complete picture and do intensive research. Definitively not for a forum post.
CREATE TABLE #Split
(
ID INT,
Texts NVARCHAR(100),Names nvarchar(100)
)
INSERT INTO #Split (ID,TExts,Names)
SELECT 1,'136;#BRZ Brazil','136;#Name3' UNION
SELECT 2,'135;#BRZ Brazil;#260;#VEN Venezuela','136;#Name1;#260;#Name2'
SELECT ID,
MAX(CASE WHEN st.ItemNumber % 2 = 1 THEN REPLACE( st.Item, '#', '') END) AS TextID,
MAX(CASE WHEN st.ItemNumber % 2 = 0 THEN REPLACE( st.Item, '#', '') END) AS Texts,
MAX(CASE WHEN st.ItemNumber % 2 = 1 THEN REPLACE( sn.Item, '#', '') END) AS NameID,
MAX(CASE WHEN st.ItemNumber % 2 = 0 THEN REPLACE( sn.Item, '#', '') END) AS Names
FROM #Split
CROSS APPLY dbo.DelimitedSplitN4K(Texts, ';') st
CROSS APPLY dbo.DelimitedSplitN4K(Names, ';') sn
WHERE st.ItemNumber = sn.ItemNumber
GROUP BY ID, (st.ItemNumber - 1) / 2;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply