How to get Last Occurance of % value in the string...
-
Hello Good Morning
How to get Last Occurance of % value in the string... please help me with this situation
CREATE TABLE #SAMPLE (description varchar(300))
INSERT INTO #SAMPLE VALUES ('1%')
INSERT INTO #SAMPLE VALUES ('1')
INSERT INTO #SAMPLE VALUES ('10%')
INSERT INTO #SAMPLE VALUES ('1.00%')
INSERT INTO #SAMPLE VALUES ('10.00%')
INSERT INTO #SAMPLE VALUES ('7% ICU')
INSERT INTO #SAMPLE VALUES ('>1% of UPENN ')
INSERT INTO #SAMPLE VALUES ('1% of amount prepaid')
INSERT INTO #SAMPLE VALUES ('ICU: 7% of UPENN')
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: 1% UPENN')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN or SKINCARE')
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: > of 1% UPENN + 1% UPENN @ 10/1/22, or 1% UPENN + 4')
INSERT INTO #SAMPLE VALUES ('sometestvaluewithout anypercent')
INSERT INTO #SAMPLE VALUES ('5% ***No PPP due on or after 84th PMT if provisions A & B in Note are met')
INSERT INTO #SAMPLE VALUES ('>1% of UPENN + 1% of scheduled UPENN at 4/1/2022 or SKINCARE + 1% of scheduled UPENN at 4/1/2022')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN+1% of scheduled UPENN@ 3/1/2028,or SKINCARE + 1% of scheduled UPENN@ 3/1/2028')
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: See Note for Treasury Rate Computation >1% or SKINCARE; after SKINCARE, 1%; no ppp last 90 days')
INSERT INTO #SAMPLE VALUES ('>1% or SKINCARE, After SKINCARE, 1%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN +1% of scheduled UPENN at 6/1/2022, or SKINCARE + 1% of scheduled UPENN at 6/1/2022; after SKINCARE, 1% UPENN; no ppp last 90 days')
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: Y.M.= 7.92 >1% or SKINCARE, After SKINCARE, 1%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ')
INSERT INTO #SAMPLE VALUES ('Greater of 1.0% of UPENN + 1.0% of scheduled UPENN at 5/1/2027, or SKINCARE + 1.0% of scheduled UPENN at 5/1/2027; after SKINCARE, 1.0% UPENN; no ppp last 90 days')
CREATE TABLE #expectedoutcome (descriptionOutcome varchar(300))
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('10%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('10%')
INSERT INTO #expectedoutcome VALUES ('7%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('7%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('')
INSERT INTO #expectedoutcome VALUES ('5%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('7%')
INSERT INTO #expectedoutcome VALUES ('3%')
INSERT INTO #expectedoutcome VALUES ('11%')
INSERT INTO #expectedoutcome VALUES ('21%')
INSERT INTO #expectedoutcome VALUES ('1%')
INSERT INTO #expectedoutcome VALUES ('2%')
- asita - Tuesday, May 29, 2018 7:37 AM
Reverse the string and look for the first occurrence. Use the length of the string to determine the position.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
I tried that but it is nnot working properly (LEN(description) - CHARINDEX('%',REVERSE(description)))
can you please provide any sample code
Thank you
-
Can you please check the expected results as those do not match the sample data.
😎Here is an example of reverse charindex, in this case extracting 6 characters including the percentage sign:
SELECTCHARINDEX(CHAR(37),REVERSE(S.[description]),1),REVERSE(SUBSTRING(REVERSE(S.[description]),CHARINDEX(CHAR(37),REVERSE(S.[description]),1),6)),S.[description]FROM #SAMPLE S -
Here's what appears to be working code:
CREATE TABLE #SAMPLE (
[description] varchar(300)
);
INSERT INTO #SAMPLE VALUES ('1%');
INSERT INTO #SAMPLE VALUES ('1');
INSERT INTO #SAMPLE VALUES ('10%');
INSERT INTO #SAMPLE VALUES ('1.00%');
INSERT INTO #SAMPLE VALUES ('10.00%');
INSERT INTO #SAMPLE VALUES ('7% ICU');
INSERT INTO #SAMPLE VALUES ('>1% of UPENN ');
INSERT INTO #SAMPLE VALUES ('1% of amount prepaid');
INSERT INTO #SAMPLE VALUES ('ICU: 7% of UPENN');
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: 1% UPENN');
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN or SKINCARE');
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: > of 1% UPENN + 1% UPENN @ 10/1/22, or 1% UPENN + 4');
INSERT INTO #SAMPLE VALUES ('sometestvaluewithout anypercent');
INSERT INTO #SAMPLE VALUES ('5% ***No PPP due on or after 84th PMT if provisions A & B in Note are met');
INSERT INTO #SAMPLE VALUES ('>1% of UPENN + 1% of scheduled UPENN at 4/1/2022 or SKINCARE + 1% of scheduled UPENN at 4/1/2022');
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN+1% of scheduled UPENN@ 3/1/2028,or SKINCARE + 1% of scheduled UPENN@ 3/1/2028');
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: See Note for Treasury Rate Computation >1% or SKINCARE; after SKINCARE, 1%; no ppp last 90 days');
INSERT INTO #SAMPLE VALUES ('>1% or SKINCARE, After SKINCARE, 1%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ');
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN +1% of scheduled UPENN at 6/1/2022, or SKINCARE + 1% of scheduled UPENN at 6/1/2022; after SKINCARE, 1% UPENN; no ppp last 90 days');
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: Y.M.= 7.92 >1% or SKINCARE, After SKINCARE, 1%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ');
INSERT INTO #SAMPLE VALUES ('Greater of 1.0% of UPENN + 1.0% of scheduled UPENN at 5/1/2027, or SKINCARE + 1.0% of scheduled UPENN at 5/1/2027; after SKINCARE, 1.0% UPENN; no ppp last 90 days');SELECT S.[description],
LTRIM(RTRIM(
CASE
WHEN LEN(S.[description]) = 1 AND R.StringPos IS NULL THEN S.[description] + '%'
WHEN R.StringPos IS NULL THEN ''
ELSE REVERSE(SUBSTRING(REVERSE(S.[description]), R.StringPos + 1, ISNULL(SP.StringPos, LEN(S.[description])) - R.StringPos)) + '%'
END
)) AS PercentValue
FROM #SAMPLE AS S
CROSS APPLY (
SELECT NULLIF(CHARINDEX('%', REVERSE(S.[description])), 0) AS StringPos
) AS R
CROSS APPLY (
SELECT NULLIF(CHARINDEX(' ', REVERSE(S.[description]), ISNULL(R.StringPos, LEN(S.[description]))), 0) AS StringPos
) AS SPDROP TABLE #SAMPLE;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thank you SSC Guru
here is correct code that matches expected output please help me
CREATE TABLE #SAMPLE (description varchar(300))
INSERT INTO #SAMPLE VALUES ('1%')
INSERT INTO #SAMPLE VALUES ('1')
INSERT INTO #SAMPLE VALUES ('10%')
INSERT INTO #SAMPLE VALUES ('1.00%')
INSERT INTO #SAMPLE VALUES ('10.00%')
INSERT INTO #SAMPLE VALUES ('7% ICU')
INSERT INTO #SAMPLE VALUES ('>1% of UPENN ')
INSERT INTO #SAMPLE VALUES ('1% of amount prepaid')
INSERT INTO #SAMPLE VALUES ('ICU: 7% of UPENN')
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: 1% UPENN')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN or SKINCARE')
INSERT INTO #SAMPLE VALUES ('PrepaSKINCAREent terms: > of 1% UPENN + 1% UPENN @ 10/1/22, or 1% UPENN + 4')
INSERT INTO #SAMPLE VALUES ('sometestvaluewithout anypercent')
INSERT INTO #SAMPLE VALUES ('5% ***No PPP due on or after 84th PMT if provisions A & B in Note are met')
INSERT INTO #SAMPLE VALUES ('>8% of UPENN + 7% of scheduled UPENN at 4/1/2022 or SKINCARE + 1% of scheduled UPENN at 4/1/2022')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN+1% of scheduled UPENN@ 3/1/2028,or SKINCARE + 7% of scheduled UPENN@ 3/1/2028')
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: See Note for Treasury Rate Computation >1% or SKINCARE; after SKINCARE, 3%; no ppp last 90 days')
INSERT INTO #SAMPLE VALUES ('>1% or SKINCARE, After SKINCARE, 11%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ')
INSERT INTO #SAMPLE VALUES ('Greater of 1% of UPENN +1% of scheduled UPENN at 6/1/2022, or SKINCARE + 1% of scheduled UPENN at 6/1/2022; after SKINCARE, 21% UPENN; no ppp last 90 days')
INSERT INTO #SAMPLE VALUES ('Prepay Terms Descr: Y.M.= 7.92 >7% or SKINCARE, After SKINCARE, 1%; no PPP last 90 days Treasury Coupon Rate to be calculated at time of payoff ')
INSERT INTO #SAMPLE VALUES ('Greater of 6.0% of UPENN + 7.0% of scheduled UPENN at 5/1/2027, or SKINCARE + 1.0% of scheduled UPENN at 5/1/2027; after SKINCARE, 2.0% UPENN; no ppp last 90 days')
-
Using Jeff Moden's et al's splitter makes this very easy. Split on spaces, capture words containing '%':
SELECT *
FROM #SAMPLE
OUTER APPLY (
SELECT TOP 1 *
FROM dbo.DelimitedSplit8K(description,' ')
WHERE Item LIKE '%=%%' ESCAPE '='
ORDER BY ItemNumber DESC
) x1
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - ChrisM@Work - Tuesday, May 29, 2018 10:07 AM
The ORDER BY itemNumber causes a TOP N sort in the execution plan. You can get rid of it using REVERSE:
SELECT
description, ItemNumber, Item = REVERSE(Item)
FROM #SAMPLE
OUTER APPLY
(
SELECT TOP 1 *
FROM dbo.DelimitedSplit8K(REVERSE(description),' ')
WHERE Item LIKE '%=%%' ESCAPE '='
ORDER BY ItemNumber
) x1;
I don't know, however, if the REVERSE will kill the performance in the same way that concatenation does (and I don't have time to test at the moment).
Also - being that this SQL 2012 I would go with Eirikur' DelimitedSplit8K_LEAD 😀"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- asita - Tuesday, May 29, 2018 9:24 AM
Appreciate the thanks, but what exactly are you asking for at this point? Doesn't the above set of INSERTs match the set you originally supplied?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, May 30, 2018 10:40 AM
Check the last record in #Sample from the initial post:
Greater of 1.0% of UPENN + 1.0% of scheduled UPENN at 5/1/2027, or SKINCARE + 1.0% of scheduled UPENN at 5/1/2027; after SKINCARE, 1.0% UPENN; no ppp last 90 days
It's a bit different from the larter version , as quoted above.Still none of the versions contains a string "2%", as per #expectedresults
_____________
Code for TallyGenerator -
Also,
row #2 contains the string '1'.
No '%' in there.
Row #2 in #expectedoutcome contains '1%'
How does it correlate?_____________
Code for TallyGenerator - Alan.B - Tuesday, May 29, 2018 11:29 AMChrisM@Work - Tuesday, May 29, 2018 10:07 AM
The ORDER BY itemNumber causes a TOP N sort in the execution plan. You can get rid of it using REVERSE:
SELECT
description, ItemNumber, Item = REVERSE(Item)
FROM #SAMPLE
OUTER APPLY
(
SELECT TOP 1 *
FROM dbo.DelimitedSplit8K(REVERSE(description),' ')
WHERE Item LIKE '%=%%' ESCAPE '='
ORDER BY ItemNumber
) x1;I don't know, however, if the REVERSE will kill the performance in the same way that concatenation does (and I don't have time to test at the moment).
Also - being that this SQL 2012 I would go with Eirikur' DelimitedSplit8K_LEAD 😀This does not work for string
>1% of UPENN
This script returns '>1%', expected outcome is '1%'
_____________
Code for TallyGenerator -
This one should return the expected result.
SELECT *,
COALESCE(
RIGHT([StringBeforeLast%], NULLIF(patindex('%[^0-9,.]%', REVERSE([StringBeforeLast%])),0) -1),
[StringBeforeLast%],
'') + '%' ActualOutcome
FROM (
select S.description
,datalength(S.description) - nullif(CHARINDEX('%', reverse(S.description)), 0 ) [CharsBeforeLast%]
, SUBSTRING(S.description, 1, datalength(S.description) - nullif(CHARINDEX('%', reverse(S.description)), 0 ) ) [StringBeforeLast%],
O.descriptionOutcome
from #Sample S
inner join #expectedoutcome O on O.RowNo = S.RowNo
) DTRemove " + '%' " from ActualOutcome, if you need just a number.
_____________
Code for TallyGenerator - Sergiy - Friday, June 1, 2018 6:40 AM
I gave you a query that will do the job. You can easily change out the data being supplied on your own and run the query against it. If you are expecting a 2% to appear at some point, then your data has to contain that value. Try the query with your updated set of data and then post back the exact set of data and query that you ran if you don't succeed, and we can troubleshoot from there.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Sergiy - Friday, June 1, 2018 7:05 AMAlan.B - Tuesday, May 29, 2018 11:29 AMChrisM@Work - Tuesday, May 29, 2018 10:07 AM
The ORDER BY itemNumber causes a TOP N sort in the execution plan. You can get rid of it using REVERSE:
SELECT
description, ItemNumber, Item = REVERSE(Item)
FROM #SAMPLE
OUTER APPLY
(
SELECT TOP 1 *
FROM dbo.DelimitedSplit8K(REVERSE(description),' ')
WHERE Item LIKE '%=%%' ESCAPE '='
ORDER BY ItemNumber
) x1;I don't know, however, if the REVERSE will kill the performance in the same way that concatenation does (and I don't have time to test at the moment).
Also - being that this SQL 2012 I would go with Eirikur' DelimitedSplit8K_LEAD 😀This does not work for string
>1% of UPENN
This script returns '>1%', expected outcome is '1%'
Ah - I didn't fully get the requirement. I guess this would work.
SELECT
description, ItemNumber, item = SUBSTRING(r.item, rs.p, CHARINDEX('%',r.item,rs.p+1))
FROM #SAMPLE
OUTER APPLY
(
SELECT TOP 1 *
FROM dbo.DelimitedSplit8K(REVERSE(description),' ')
WHERE Item LIKE '%=%%' ESCAPE '='
ORDER BY ItemNumber
) x1
CROSS APPLY (VALUES (REVERSE(x1.Item))) r(item)
CROSS APPLY (VALUES (PATINDEX('%[0-9]%',r.item))) rs(p);More importantly - I was trying to demonstrate how to pull this off using DelimitedSplit8K without a sort in the execution plan.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply