Parse Data in SQL

Question

Parse Data in SQL

sks_989

SSC Eights!

Points: 843
More actions
June 9, 2017 at 9:28 am

#323967

I have data into field (SS)(DD)(GG) i want parse that field into 3 different column. Is Substring work or there is other way i can parse this field.
Three field should be like this
Field 1 Field 2 Field 3
SS DD GG

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

sks_989 - Friday, June 9, 2017 9:28 AM
I have data into field (SS)(DD)(GG) i want parse that field into 3 different column. Is Substring work or there is other way i can parse this field.
Three field should be like this
Field 1 Field 2 Field 3
SS DD GG

Please do not cross-post in multiple forums, it fragments replies and wastes people's time. Answers here please.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

DECLARE @x VARCHAR(15) = '(SS)(DD)(GG)';

SELECT col1 = SUBSTRING(@x, 2, 2) , col2 = SUBSTRING(@x, 6, 2) , col3 = SUBSTRING(@x, 10, 2);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sks_989 SSC Eights! Points: 843 More actions · Answer 3

It can have 3 character in that data too like that way (SS)(DDD)(GG) is there deliminator function in SQL

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

sks_989 - Friday, June 9, 2017 9:56 AM
It can have 3 character in that data too like that way (SS)(DDD)(GG) is there deliminator function in SQL

That would have been useful information in your first post, don't you think?
As you have only three items, you can force the PARSENAME() function to do this work for you:
WITH replaced AS ( SELECT val = REPLACE(REPLACE(REPLACE(@x, ')(', '.'), '(', ''), ')', '') ) SELECT Col1 = PARSENAME(replaced.val, 3) , Col2 = PARSENAME(replaced.val, 2) , Col3 = PARSENAME(replaced.val, 1) FROM replaced;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sks_989 SSC Eights! Points: 843 More actions · Answer 5

Max column is 4 but that data can have like this so what is best way to do

(DDD)(LL)(GGGG)(HH)
(FF)(GG)
(GG)
(GG)(HH)(LL)

output wants as
Col1 Col2 Col3 Col4
DDD LL GGGG HH
FF GG
GG
GG HH LL

debasis.yours Hall of Fame Points: 3457 More actions · Answer 6

Hi,

If you are using SQL 2016 (compatibility level 130), you can achieve this by the following way:

DECLARE @Input VARCHAR(100) = '(DD)(GG)(BBB)(AAA)'
SELECT @Input = REPLACE(@Input,')','')
SELECT * FROM STRING_SPLIT(SUBSTRING(@Input, 2, LEN(@Input)), '(')

But I think you will need to write a function to make it work in earlier versions.

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

sks_989 - Friday, June 9, 2017 11:23 AM
Max column is 4 but that data can have like this so what is best way to do
(DDD)(LL)(GGGG)(HH)
(FF)(GG)
(GG)
(GG)(HH)(LL)
output wants as
Col1 Col2 Col3 Col4
DDD LL GGGG HH
FF GG
GG
GG HH LL

I can't be bothered helping you any further because you appear unprepared to do anything other than drip-feed your requirements, one post at a time. Every change to your requirements requires a change in approach to solve it & that's wasting my time.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sks_989 SSC Eights! Points: 843 More actions · Answer 8

I got answer.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
String VARCHAR(20) NOT NULL
);
INSERT #TestData (String) VALUES ('(SS)(DD)(GG)'), ('(DD)(FFF)(GG)(HH)'), ('(AS)(RT)');

SELECT
td.String,
cs.CleanString,
cc.ColumnCount,
Col1 = PARSENAME(cs.CleanString, cc.ColumnCount),
Col2 = PARSENAME(cs.CleanString, cc.ColumnCount -1),
Col3 = PARSENAME(cs.CleanString, cc.ColumnCount -2),
Col4 = PARSENAME(cs.CleanString, cc.ColumnCount -3)
FROM
#TestData td
CROSS APPLY ( VALUES (REPLACE(REPLACE(REPLACE(td.String, ')(', '.'), '(', ''), ')', '')) ) cs (CleanString)
CROSS APPLY ( VALUES (LEN(cs.CleanString) - LEN(REPLACE(cs.CleanString, '.', '')) + 1) ) cc (ColumnCount);

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

sks_989 - Friday, June 9, 2017 12:36 PM
I got answer.
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
String VARCHAR(20) NOT NULL
);
INSERT #TestData (String) VALUES ('(SS)(DD)(GG)'), ('(DD)(FFF)(GG)(HH)'), ('(AS)(RT)');
SELECT
td.String,
cs.CleanString,
cc.ColumnCount,
Col1 = PARSENAME(cs.CleanString, cc.ColumnCount),
Col2 = PARSENAME(cs.CleanString, cc.ColumnCount -1),
Col3 = PARSENAME(cs.CleanString, cc.ColumnCount -2),
Col4 = PARSENAME(cs.CleanString, cc.ColumnCount -3)
FROM
#TestData td
CROSS APPLY ( VALUES (REPLACE(REPLACE(REPLACE(td.String, ')(', '.'), '(', ''), ')', '')) ) cs (CleanString)
CROSS APPLY ( VALUES (LEN(cs.CleanString) - LEN(REPLACE(cs.CleanString, '.', '')) + 1) ) cc (ColumnCount);

That's what Jason posted here: https://www.sqlservercentral.com/Forums/FindPost1880731.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions