March 23, 2017 at 6:08 am
Hi, I have tried using Charindex/Patindex, I can't get exact result, Any help much appreciated.
I need to extract number from text that too till number end. I don't need all numbers from text. Only specific portion
Create table #TEMP
(
ID Varchar(200)
)
Insert Into #TEMP Values ('ABC205916_DAN')
Insert Into #TEMP Values ('ABC243296')
Insert Into #TEMP Values ('ABC222249_DAN25')
Insert Into #TEMP Values ('IN217465_v99')
Insert Into #TEMP Values ('ABC#243296')
OUTPUT
205916
243296
222249
217465
243296
March 23, 2017 at 6:25 am
Yes, I am also facing the same issue.
Experts, Please help me and koti. Thanks.
March 23, 2017 at 6:33 am
what are the rules that specifically exclude 25 from ('ABC222249_DAN25') and exclude 99 from ('IN217465_v99') ?
are there any other possible formats that will need to excluded?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2017 at 6:37 am
Good question, DAN25 and V99 id of the person, some cases it is appended with name and some cases its not updated. As of now don't have other formats, if any in future I will let you know. Thanks!
March 23, 2017 at 6:40 am
There's many ways to tackle this, here's a few:Sample data
CREATE TABLE #TEMP(ID Varchar(200));
INSERT #TEMP
VALUES ('ABC205916_DAN'),('ABC243296'),('ABC222249_DAN25'),('IN217465_v99'),('ABC#243296');
GO
If it's always 6 numbers you're looking for you could do this:
SELECT
ID,
ID_NEW =
SUBSTRING
(
ID,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', ID),
6
)
FROM #TEMP;
If it's always the first group of numbers you can do this
WITH number_start AS
(
SELECT
ID,
start = SUBSTRING(ID, PATINDEX('%[0-9]%', ID), 200)
FROM #TEMP
)
SELECT
ID,
ID_NEW =
SUBSTRING
(
start, 1,
ISNULL(NULLIF(PATINDEX('%[^0-9]%', start)-1,-1), 200)
)
FROM number_start;
This may also work; using PatternSplitCM:
SELECT
ID,
ID_NEW = item
FROM #TEMP
CROSS APPLY dbo.PatternSplitCM(ID, '%[0-9]%')
WHERE ItemNumber = 2;
-- Itzik Ben-Gan 2001
March 23, 2017 at 7:18 am
-- I'd use a function for this, to mask the complexityfrom your queries:
CREATE FUNCTION dbo.IF_GrabFirstNumericChunk
( @MyString VARCHAR(100) )
RETURNS TABLE AS RETURN
WITH N0 AS (SELECT n = 0 FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n)),
_Tally AS (SELECT TOP(LEN(@MyString)) rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM N0),
Grouper AS (
SELECT rn, grp = rn - ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM _Tally
WHERE SUBSTRING(@MyString,rn,1) IN ('0','1','2','3','4','5','6','7','8','9')),
Agg AS (
SELECT grp, [Start] = MIN(rn), [End] = MAX(rn)
FROM Grouper
GROUP BY grp)
SELECT TOP(1) FirstNumericChunk = SUBSTRING(@MyString,[Start], [End]-[Start]+1)
FROM Agg ORDER BY grp
GO
Create table #TEMP (ID Varchar(200))
Insert Into #TEMP Values ('ABC205916_DAN')
Insert Into #TEMP Values ('ABC243296')
Insert Into #TEMP Values ('ABC222249_DAN25')
Insert Into #TEMP Values ('IN217465_v99')
Insert Into #TEMP Values ('ABC#243296')
SELECT *
FROM #TEMP
CROSS APPLY dbo.IF_GrabFirstNumericChunk(ID)
[/code]
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply