Need to split the row values

Question

Need to split the row values

p.shabbir

SSCrazy

Points: 2404
More actions
June 3, 2016 at 5:32 am

#308163

Hi All,
I have a below row in a table. I need to split this row
It need to be split after this value. see below be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16
Thanx In advnc.

Viewing 15 posts - 1 through 15 (of 21 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

WITH MyData AS (SELECT MyString = '11157243 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 ISASANTAELLA@HOTMAIL.COM c1e1b966-4233-11d4-85dc-080009daf0d2 373294099 XXXXX4099 ISABEL SANTAELLA DE KUBLER ISABEL SANTAELLA DE KUBLER Y MORI N ENG D US N F 0 02/09 N B S P 58733 3 7 A MILESTONE_DB C29C6B C2663B CTRL-I 20160526_MRCC_DOM_ENG be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16 11157246 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 DEBORAH.BEAUCHAMP@HOTMAIL.COM c1e1bd94-4233-11d4-85dc-080009daf0d2 373294123 XXXXX4123 DEBORAH KOONS DEBORAH KOONS Y H007 N ENG D US N F 48 N S G P 26358 21 29 A MILESTONE_DS C2755B C28D4E PBIZ 20160526_MRCC_DOM_ENG applynow.chase.com/FlexAppWeb/renderApp.do?SPID=FK6X&CELL=60YB&AFFID=&CLICK=&CID=&PROMO=DF01 applynow.chase.com/FlexAppWeb/pricing.do?card=FK6X&page_type=appterms creditcards.chase.com/a1/marriottpremier/Biz100k5ksh-offer-details-May16')

SELECT

m.MyString,

x.NewString

FROM MyData m

CROSS APPLY (VALUES

(LEFT(m.MyString, CHARINDEX('11157246',m.MyString)-2)),

(SUBSTRING(m.MyString,CHARINDEX('11157246',m.MyString),8000))

) x (NewString)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

ChrisM@Work (6/3/2016)
WITH MyData AS (SELECT MyString = '11157243 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 ISASANTAELLA@HOTMAIL.COM c1e1b966-4233-11d4-85dc-080009daf0d2 373294099 XXXXX4099 ISABEL SANTAELLA DE KUBLER ISABEL SANTAELLA DE KUBLER Y MORI N ENG D US N F 0 02/09 N B S P 58733 3 7 A MILESTONE_DB C29C6B C2663B CTRL-I 20160526_MRCC_DOM_ENG be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16 11157246 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 DEBORAH.BEAUCHAMP@HOTMAIL.COM c1e1bd94-4233-11d4-85dc-080009daf0d2 373294123 XXXXX4123 DEBORAH KOONS DEBORAH KOONS Y H007 N ENG D US N F 48 N S G P 26358 21 29 A MILESTONE_DS C2755B C28D4E PBIZ 20160526_MRCC_DOM_ENG applynow.chase.com/FlexAppWeb/renderApp.do?SPID=FK6X&CELL=60YB&AFFID=&CLICK=&CID=&PROMO=DF01 applynow.chase.com/FlexAppWeb/pricing.do?card=FK6X&page_type=appterms creditcards.chase.com/a1/marriottpremier/Biz100k5ksh-offer-details-May16') SELECT m.MyString, x.NewString FROM MyData m CROSS APPLY (VALUES (LEFT(m.MyString, CHARINDEX('11157246',m.MyString)-2)), (SUBSTRING(m.MyString,CHARINDEX('11157246',m.MyString),8000))
) x (NewString)

Excellent Chris, Thank you very much.

But I have 396 rows in table I have to do for all this. How can I do this?

Much appreciated....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Do you have a means of detecting the split point?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 4

ChrisM@Work (6/3/2016)
Do you have a means of detecting the split point?

After

"be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16" value

in the above example the record must be splitted.

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

p.shabbir (6/3/2016)
ChrisM@Work (6/3/2016)
Do you have a means of detecting the split point?
11157243 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 ISASANTAELLA@HOTMAIL.COM c1e1b966-4233-11d4-85dc-080009daf0d2 373294099 XXXXX4099 ISABEL SANTAELLA DE KUBLER ISABEL SANTAELLA DE KUBLER Y MORI N ENG D US N F 0 02/09 N B S P 58733 3 7 A MILESTONE_DB C29C6B C2663B CTRL-I 20160526_MRCC_DOM_ENG be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16 11157246 1224314 20160523 05/22/2016 20160526 GLOB 4,000 16 72 DEBORAH.BEAUCHAMP@HOTMAIL.COM c1e1bd94-4233-11d4-85dc-080009daf0d2 373294123 XXXXX4123 DEBORAH KOONS DEBORAH KOONS Y H007 N ENG D US N F 48 N S G P 26358 21 29 A MILESTONE_DS C2755B C28D4E PBIZ 20160526_MRCC_DOM_ENG applynow.chase.com/FlexAppWeb/renderApp.do?SPID=FK6X&CELL=60YB&AFFID=&CLICK=&CID=&PROMO=DF01 applynow.chase.com/FlexAppWeb/pricing.do?card=FK6X&page_type=appterms creditcards.chase.com/a1/marriottpremier/Biz100k5ksh-offer-details-May16
After
"be.wylei.com/3dadf94363998b3bb263e8944efa95b4.smartclick?wid=${Profile.CustomerKey}&wcid=${CAMPAIGN_KEY}&wseg=${VSEG} applynow.chase.com/FlexAppWeb/pricing.do?card=FK6S&page_type=appterms creditcards.chase.com/a1/marriottpremier/200SC-offer-details-May16" value
in the above example the record must be splitted.
Thanks.

You've done this by eye. Can you describe a rule or set of rules to find the split point?

For instance, the example you posted has an 8-digit number as the first space-delimited element. It looks like an identifier, a row identifier. At the point where the row is split, the first element of the new row also has an 8-digit number as the first space-delimited element, and it's numerically similar: 11157243 compared with 11157246.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 6

Ok. I have modified the query like below. But getting error Like "Invalid length parameter passed to the LEFT or SUBSTRING function".

WITH MyData AS (SELECT SRC_ERROR_OUTPUT FROM REJ.FILE_READ_REJECTS WHERE FILE_TAG_ID = '1002667639')

SELECT

m.SRC_ERROR_OUTPUT,

x.NewString

FROM MyData m

CROSS APPLY (VALUES

(LEFT(m.SRC_ERROR_OUTPUT, CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT)-2)),

(SUBSTRING(m.SRC_ERROR_OUTPUT,CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT),8000))

) x (NewString)

Thanx In advnc.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

p.shabbir (6/3/2016)
Ok. I have modified the query like below. But getting error Like "Invalid length parameter passed to the LEFT or SUBSTRING function".
WITH MyData AS (SELECT SRC_ERROR_OUTPUT FROM REJ.FILE_READ_REJECTS WHERE FILE_TAG_ID = '1002667639')
SELECT
m.SRC_ERROR_OUTPUT,
x.NewString
FROM MyData m
CROSS APPLY (VALUES
(LEFT(m.SRC_ERROR_OUTPUT, CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT)-2)),
(SUBSTRING(m.SRC_ERROR_OUTPUT,CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT),8000))
) x (NewString)
Thanx In advnc.

Incorrect usage of CHARINDEX(). Study how to use it here.

Can you answer the question in my last post?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 8

ChrisM@Work (6/3/2016)
p.shabbir (6/3/2016)
Ok. I have modified the query like below. But getting error Like "Invalid length parameter passed to the LEFT or SUBSTRING function".
WITH MyData AS (SELECT SRC_ERROR_OUTPUT FROM REJ.FILE_READ_REJECTS WHERE FILE_TAG_ID = '1002667639')
SELECT
m.SRC_ERROR_OUTPUT,
x.NewString
FROM MyData m
CROSS APPLY (VALUES
(LEFT(m.SRC_ERROR_OUTPUT, CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT)-2)),
(SUBSTRING(m.SRC_ERROR_OUTPUT,CHARINDEX(SRC_ERROR_OUTPUT,m.SRC_ERROR_OUTPUT),8000))
) x (NewString)
Thanx In advnc.
Incorrect usage of CHARINDEX(). Study how to use it here.
Can you answer the question in my last post?

Thnx Chris,

Let me explain clearly.

These records are loaded while processing the text file using ssis. If the file is missing delimiter, the next row will append it to the current row.Hence, records are rejected and loaded into rejects table.

For Ex : File has rows like below.

THis is one record

if delimiter is missing then it appends second row to the first row and load into the table like below.

File is Tab delimited. Hope this helps.

Thnx.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Thanks but that doesn't answer the question. You can determine the split point by eye because you know what to look for. What I'm trying to figure out is a rule or set of rules to determine the split point programmatically. Once you've got that, the rest is trivial.

There's the point I raised earlier. You might also look at the string length, or you could count spaces or tabs or whatever between the elements of the string.

The data you've posted doesn't hold all of the information of the original so I can't do this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 10

ChrisM@Work (6/3/2016)
Thanks but that doesn't answer the question. You can determine the split point by eye because you know what to look for. What I'm trying to figure out is a rule or set of rules to determine the split point programmatically. Once you've got that, the rest is trivial.
There's the point I raised earlier. You might also look at the string length, or you could count spaces or tabs or whatever between the elements of the string.
The data you've posted doesn't hold all of the information of the original so I can't do this.

Thnx for the patience Chris,

Split point is after this value. So, 11157246 should come as a new row.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Each of the rows you have to split have this string in common?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 12

ChrisM@Work (6/3/2016)
Each of the rows you have to split have this string in common?

Much appreciated chris, for the prompt responses.

Not common.

To make it simple. Find the below create table script and sample data.

Deleted scripts and sample data.

Like this 396 rows to be splitted.

Thanx.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Look through your data to find a common rule which will identify the split location. It might be the nth tab, it might be immediately following "200SC-offer-details-May16", it might be the exact middle of each string. You have the data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.shabbir SSCrazy Points: 2404 More actions · Answer 14

ChrisM@Work (6/3/2016)
Look through your data to find a common rule which will identify the split location. It might be the nth tab, it might be immediately following "200SC-offer-details-May16", it might be the exact middle of each string. You have the data.

Yes, but not getting how to achieve this.