April 17, 2016 at 6:13 pm
I have a table that has data like
DDR Return PP12345
DDR Resturns PP12356
DDR Retunrs PP12367
I need to be able to just show the last numbers. The problem is the last numbers could be anything from 5 to 7 numbers. Also because the data is hand loaded it can have spelling mistakes. How can I delete all the characters regardless of how many before the PP. I can then just replace the PP with '' although having a 1 step process to delete up to and including the PP would be great.
I have tried the following:
,REPLACE ([TransNarrative],'DDR RETURN PP','') as Transnarrative
This works fine if there are no spelling mistakes but does not work if there are spelling mistakes.
I then tried
,LEFT([TransNarrative], CHARINDEX('P',[TransNarrative])-1) as TransNarrative1
and got the error message:
Msg 537, Level 16, State 2, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.
Can anyone help?
April 17, 2016 at 8:07 pm
This will do the trick (note my comments).
DECLARE @yourTable TABLE (someString varchar(100));
INSERT @yourTable
VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367');
SELECT someString,
s1 = SUBSTRING(someString,PATINDEX('%PP[0-9][0-9]%', someString),8000), -- if you need the P's
s2 = SUBSTRING(someString,PATINDEX('%PP[0-9][0-9]%', someString)+2,8000) -- if you don't need the P's
FROM @yourTable;
Edit: Just realized this is a 2005 server, PATINDEX won't work. Here's the 2005 solution:
DECLARE @yourTable TABLE (someString varchar(100));
INSERT @yourTable
VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367');
SELECT someString,
s1 = SUBSTRING(someString,CHARINDEX('PP', someString),8000), -- if you need the P's
s2 = SUBSTRING(someString,CHARINDEX('PP', someString)+2,8000) -- if you don't need the P's
FROM @yourTable;
The only problem here is if the letters "PP" appear somewhere else in the string. Another way to approach this would be to use a tally table as shown below:
DECLARE @yourTable TABLE (someString varchar(100));
INSERT @yourTable
VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367');
WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1 a,E1 b,E1 c,E1 d)
SELECT someString, extracted = SUBSTRING(someString, N+2, 8000)
FROM @yourTable y
CROSS APPLY iTally t
WHERE N < (LEN(someString))-2
AND SUBSTRING(someString, N, 4) LIKE '%PP[0-9][0-9]%';
-- Itzik Ben-Gan 2001
April 17, 2016 at 8:46 pm
SSCommitted, Many thanks I have tried the "s2 = SUBSTRING(someString,CHARINDEX('PP', someString)+2,8000)" option and it works well for the entries in question However for some entries that have no PP in them it cut off the first character. Not really an issue as I am only interested in those with the PP in them. To help me learn SQL a bit better, can you tell me what the 8000 stand for? I am assuming the script says to substring (or keep as a string) anything after PP and 2 characters (ie the PP). Is that correct?:cool:
April 17, 2016 at 9:17 pm
les.61 (4/17/2016)
SSCommitted, Many thanks I have tried the "s2 = SUBSTRING(someString,CHARINDEX('PP', someString)+2,8000)" option and it works well for the entries in question However for some entries that have no PP in them it cut off the first character. Not really an issue as I am only interested in those with the PP in them. To help me learn SQL a bit better, can you tell me what the 8000 stand for? I am assuming the script says to substring (or keep as a string) anything after PP and 2 characters (ie the PP). Is that correct?:cool:
Yes - that's correct. The third parameter, 8000, in SUBSTRING is the length of the substring to return. Note the BOL entry for SUBSTRING. If the string is shorter than 8000 characters then it will just return everything to the end of the string which works for your requirement.
Note that, if you did not need the "PP" and just the numbers you could do this:
DECLARE @yourTable TABLE (someString varchar(100));
INSERT @yourTable
VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367');
DECLARE
@patternToFind varchar(100) = '%[0-9][0-9][0-9][0-9][0-9]%',
@patternToFindLen int = 5;
WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1 a,E1 b,E1 c,E1 d)
SELECT someString, extracted = SUBSTRING(someString, N, 8000)
FROM @yourTable y
CROSS APPLY iTally t
WHERE N < (LEN(someString))
AND SUBSTRING(someString, N, @patternToFindLen) LIKE @patternToFind;
-- Itzik Ben-Gan 2001
April 18, 2016 at 7:17 am
This might work for you.
PATINDEX is available in SQL Server 2005.
DECLARE @yourTable TABLE (someString varchar(100));
INSERT @yourTable
VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367'), ('This one does not have a number'), ('With a number but no double P before it 128561');
SELECT someString,
s2 = RIGHT( someString, PATINDEX('%[^0-9]%', REVERSE(someString)) - 1)
FROM @yourTable
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply