July 19, 2016 at 5:31 pm
Hi-
How can I grab the numeric value from string.
Numeric value if starts with 2 is going to be 16 characters long and if it starts with 1 will be 20 characters long.
ex) Recalc 2015659341589653
ex) Recalc of 2015658926358915 revised
ex) Recalc to clm 15649687415315781536 error
I only need the numeric values from the notes in the 3 examples.
Thanks,
Nina
July 19, 2016 at 7:34 pm
Here is one way to do it assuming the number is always followed by a space or is at the end of the string.
SELECT SUBSTRING(col1, PATINDEX('%[0-9]%', col1), COALESCE(NULLIF(CHARINDEX(' ', col1, PATINDEX('%[0-9]%', col1)), 0 ), DATALENGTH(col1) + 1) - PATINDEX('%[0-9]%', col1))
,col1
FROM (VALUES('ex) Recalc 2015659341589653'),
('ex) Recalc of 2015658926358915 revised'),
('ex) Recalc to clm 15649687415315781536 error')) AS tbl(col1)
July 19, 2016 at 8:11 pm
Thank you I will give it a try 🙂
The number is randomly placed so it will sometimes be in middle of the note and sometimes at the end it just depends.
Thats why I have to filter to make sure if it starts with 2 it will grab the 16 characters and if it starts with 1 it will grab the 20 characters since text may be at the end.
July 20, 2016 at 7:51 am
Another couple options:
1. You can use DigitsOnlyEE[/url]. This exactly the type of task it was designed for (note my comments):
-- How to use dbo.DigitsOnlyEE
SELECT DigitsOnly FROM dbo.DigitsOnlyEE('Recalc 2015659341589653');
SELECT DigitsOnly FROM dbo.DigitsOnlyEE('Recalc to clm 15649687415315781536 error');
-- How to use dbo.DigitsOnlyEE against a table
DECLARE @SomeTable TABLE (SomeString varchar(100));
INSERT @SomeTable VALUES ('Recalc 2015659341589653'),('Recalc of 2015658926358915 revised'),
('Recalc to clm 15649687415315781536 error');
SELECT SomeString, DigitsOnly
FROM @SomeTable
CROSS APPLY dbo.DigitsOnlyEE(SomeString);
2. Since you know the length of digits you are looking for (20 when starting with 1, 16 when starting with 2) you could simply use PATINDEX like so:
DECLARE @SomeTable TABLE (SomeString varchar(100));
INSERT @SomeTable VALUES ('Recalc 2015659341589653'),('Recalc of 2015658926358915 revised'),
('Recalc to clm 15649687415315781536 error');
DECLARE
@pat1 varchar(98) = '%1'+REPLICATE('[0-9]',19)+'%', -- 1 followed by 19 digits
@pat2 varchar(78) = '%2'+REPLICATE('[0-9]',15)+'%'; -- 2 followed by 15 digits
SELECT
SomeString,
CASE
WHEN PATINDEX(@pat1, SomeString) > 1
THEN SUBSTRING(SomeString,PATINDEX(@pat1, SomeString),20)
WHEN PATINDEX(@pat2, SomeString) > 1
THEN SUBSTRING(SomeString,PATINDEX(@pat2, SomeString),16)
END
FROM @SomeTable;
-- Itzik Ben-Gan 2001
July 20, 2016 at 8:07 am
Here's another option with an additional validation for strings without digits.
SELECT col1
,LEFT( PartialString, CHARINDEX( ' ', PartialString + ' '))
FROM (VALUES('ex) Recalc 2015659341589653'),
('ex) Recalc of 2015658926358915 revised'),
('ex) Recalc to clm 15649687415315781536 error'),
('ex) Recalc to clm no error')) AS tbl(col1)
CROSS APPLY ( SELECT SUBSTRING(col1, PATINDEX('%[0-9]%', col1 + '9'), 100))x(PartialString) --Change the 100 to the length of the column
July 20, 2016 at 8:50 am
Another example, that simply uses a CASE expression to test whether a 1 appears before a 2 in the string.
with ExampleData (AlphaNum) as (select 'Recalc 2015659341589653' union all
select 'Recalc of 2015658926358915 revised' union all
select 'Recalc to clm 15649687415315781536 error' union all
select 'Recalc of 333333333333333333333333 test')
,cte1 (AlphaNum, First1,First2)as( -- just to make the final logic read a little easier
select Alphanum, charindex('1',AlphaNum,1), charindex('2',AlphaNum,1)
from ExampleData)
select AlphaNum, first1, first2,
case when first1 = 0 and first2 = 0 then null
when first1 < first2 or first2 = 0 then substring(AlphaNum,First1,20)
when first2 < first1 or first1 = 0 then substring(AlphaNum,First2,16)
else null
end as NumString
from cte1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 20, 2016 at 9:27 am
Thank you 🙂
July 20, 2016 at 10:35 am
Yet another way, using DelimitedSplit8k[/url].
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
DROP TABLE #test;
CREATE TABLE #test
(
id INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED
,Col VARCHAR(50)
);
INSERT #test
(Col)
VALUES ('ex) Recalc 2015659341589653'),
('ex) Recalc of 2015658926358915 revised'),
('ex) Recalc to clm 15649687415315781536 error');
SELECT t.*
, split.Item
FROM #test t
CROSS APPLY dbo.udfDelimitedSplit8K(t.Col, ' ') split
WHERE split.Item >= '0'
AND split.Item < 'A';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 20, 2016 at 11:10 am
If we're busting out splitters then another way would be using PatternSplitCM[/url].
DECLARE @SomeTable TABLE (SomeString varchar(100));
INSERT @SomeTable VALUES ('Recalc 2015659341589653'),('Recalc of 2015658926358915 revised'),
('Recalc to clm 15649687415315781536 error');
SELECT SomeString, Item
FROM @SomeTable
CROSS APPLY dbo.PatternSplitCM(SomeString,'[0-9]')
WHERE [matched] = 1
--uncomment for extra protection against rogue numbers in the string:
--AND ((item LIKE '1%' AND LEN(item) = 20) OR (item LIKE '2%' AND LEN(item)=16))
-- Itzik Ben-Gan 2001
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply