November 28, 2018 at 9:17 am
I have data like this in a table
col1 , col2, col3
A,2, this is a short string no need to split
B,3, this is a test string and the length of the string should not be greater that fifty.
If the length of col3 is greater that 50 the string needs to be split and output as
B,3,this is a test string and the length of the string
B,3,should not be greater that fifty.
So the final result of the table is like this.
A,2, this is a short string no need to split
B,3,this is a test string and the length of the string
B,3,should not be greater that fifty.
Thank you !
November 28, 2018 at 9:35 am
;WITH myTable AS
(
SELECT *
FROM (VALUES ('A',2, 'this is a short string no need to split'),
('B',3, 'this is a test string and the length of the string should not be greater that fifty.'))
AS T(col1, col2, col3)
)
SELECT t.col1, t.col2, LEFT(t.col3,50) col3
FROM myTable t
UNION ALL
SELECT t.col1, t.col2, SUBSTRING(t.col3,51,1000) col3
FROM myTable t
WHERE LEN(t.col3) > 50
November 28, 2018 at 9:46 am
Jonathan AC Roberts - Wednesday, November 28, 2018 9:35 AM;WITH myTable AS
(
SELECT *
FROM (VALUES ('A',2, 'this is a short string no need to split'),
('B',3, 'this is a test string and the length of the string should not be greater that fifty.'))
AS T(col1, col2, col3)
)
SELECT t.col1, t.col2, LEFT(t.col3,50) col3
FROM myTable t
UNION ALL
SELECT t.col1, t.col2, SUBSTRING(t.col3,51,1000) col3
FROM myTable t
WHERE LEN(t.col3) > 50
Thank you for your help, but how do I convert this to read entries from a table? Col1,col2,col3 are coming from a table which has many records and if the col3 has length > 50, the string needs to be split.
November 28, 2018 at 9:57 am
Guras - Wednesday, November 28, 2018 9:46 AMJonathan AC Roberts - Wednesday, November 28, 2018 9:35 AM;WITH myTable AS
(
SELECT *
FROM (VALUES ('A',2, 'this is a short string no need to split'),
('B',3, 'this is a test string and the length of the string should not be greater that fifty.'))
AS T(col1, col2, col3)
)
SELECT t.col1, t.col2, LEFT(t.col3,50) col3
FROM myTable t
UNION ALL
SELECT t.col1, t.col2, SUBSTRING(t.col3,51,1000) col3
FROM myTable t
WHERE LEN(t.col3) > 50Thank you for your help, but how do I convert this to read entries from a table? Col1,col2,col3 are coming from a table which has many records and if the col3 has length > 50, the string needs to be split.
Just use this and replace myTable with your table name.SELECT t.col1, t.col2, LEFT(t.col3,50) col3
FROM myTable t
UNION ALL
SELECT t.col1, t.col2, SUBSTRING(t.col3,51,1000) col3
FROM myTable t
WHERE LEN(t.col3) > 50
November 28, 2018 at 10:08 am
There's also this tally table approach if the col3 source can be longer 100 and up to 5000.
DROP TABLE IF EXISTS #tmpExamples ;
SELECT *
INTO #tmpExamples
FROM (VALUES ('A',2, 'this is a short string no need to split'),
('B',3, 'this is a test string and the length of the string should not be greater that fifty.'),
('C',4, 'this is a test string and the length of the string should not be greater that fifty.Just in case there are some values even bigger than 100 characters')
) T(col1 , col2, col3)
;
WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)
,cte AS
(
SELECT
v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50 + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)
SELECT
e.col1
,e.col2
,SUBSTRING(e.col3,c.v,50)
FROM #tmpExamples e
CROSS JOIN cte c
WHERE
SUBSTRING(e.col3,c.v,50) <> ''
;
November 28, 2018 at 10:12 am
Jonathan AC Roberts - Wednesday, November 28, 2018 9:57 AMJust use this and replace myTable with your table name.SELECT t.col1, t.col2, LEFT(t.col3,50) col3
FROM myTable t
UNION ALL
SELECT t.col1, t.col2, SUBSTRING(t.col3,51,1000) col3
FROM myTable t
WHERE LEN(t.col3) > 50
This approach may split the string in the middle of words. If you want to split only at word boundaries, you'll want something more sophisticated and SQL isn't the best tool for string manipulation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 28, 2018 at 1:22 pm
gilbert delarosa - Wednesday, November 28, 2018 10:08 AMThere's also this tally table approach if the col3 source can be longer 100 and up to 5000.DROP TABLE IF EXISTS #tmpExamples ;
SELECT *
INTO #tmpExamples
FROM (VALUES ('A',2, 'this is a short string no need to split'),
('B',3, 'this is a test string and the length of the string should not be greater that fifty.'),
('C',4, 'this is a test string and the length of the string should not be greater that fifty.Just in case there are some values even bigger than 100 characters')
) T(col1 , col2, col3);
WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
),cte AS
(
SELECT
v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50 + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)SELECT
e.col1
,e.col2
,SUBSTRING(e.col3,c.v,50)
FROM #tmpExamples e
CROSS JOIN cte c
WHERE
SUBSTRING(e.col3,c.v,50) <> ''
;
This was amazing. It did exactly what I wanted to . Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply