April 8, 2014 at 4:13 am
Hi experts,
i hope my problem will be solved. I have a string and i want to get only the numbers from right.
for example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
SELECT dbo.udf_GetNumeric('111zxfggr123456') AS 'Num'
if i ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
Can any one help me out to solve this?
Thanks in advance!!
April 8, 2014 at 4:51 am
DECLARE @s-2 AS VARCHAR(100)='123756zxfggr123456';
SELECT REVERSE(
SUBSTRING(REVERSE(@s),PATINDEX('%[0-9]%',
REVERSE(@s)),PATINDEX('%[^0-9]%',
SUBSTRING(REVERSE(@s),(PATINDEX('%[0-9]%', REVERSE(@s))),LEN(@s)))-1));
___________________________
Do Not Optimize for Exceptions!
April 8, 2014 at 5:40 am
Thanks Milos...........thanks a lot...you saved my day...
April 8, 2014 at 5:58 am
That solution works but will give an error if the string consists of only numerals.
declare @rowstring as varchar(max) = '1251596483'
select
case
when isnumeric(@rowstring) = 1 then @rowstring
else
REVERSE(
SUBSTRING(REVERSE(@rowstring),PATINDEX('%[0-9]%',
REVERSE(@rowstring)),PATINDEX('%[^0-9]%',
SUBSTRING(REVERSE(@rowstring),(PATINDEX('%[0-9]%', REVERSE(@rowstring))),LEN(@rowstring)))-1))
end
That will (inelegantly) handle all numeral strings if they do occur. I gratefully acknowledge Milos for doing all the hard work.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 8, 2014 at 6:19 am
BWFC (4/8/2014)
That solution works but will give an error if the string consists of only numerals.
Thank you BWFC for the correction!
A great thing in this forum is whenever you write something which is not completely true or in some corner cases would not work someone puts appropriate comment. That's really great and continuously improve the quality of posts and comments.
Thanks again!
___________________________
Do Not Optimize for Exceptions!
April 8, 2014 at 6:55 am
This function will bring back the last number only portion of the string
😎
CREATE FUNCTION dbo.udf_GetNumEEric
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS TABLE
AS
RETURN
WITH NBR1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N))
,NUMBERS(N) AS (SELECT TOP (DATALENGTH(@strAlphaNumeric)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM NBR1 N1,NBR1 N2, NBR1 N3)
,RAW_STR AS
( SELECT
NM.N
,SUBSTRING(@strAlphaNumeric,(LEN(@strAlphaNumeric) + 1 )-NM.N,1) TCHAR
FROM NUMBERS NM
)
,NUMCHECK AS
( SELECT
RS.N
,RS.TCHAR
,CASE
WHEN UNICODE(RS.TCHAR) >= 48 AND UNICODE(RS.TCHAR) <= 57 THEN 0
ELSE 1
END AS NM_CHECK
FROM RAW_STR RS
)
,NUM_PART AS
( SELECT
NC.N
,NC.TCHAR
,SUM(NC.NM_CHECK) OVER
( PARTITION BY (SELECT NULL)
ORDER BY NC.N ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS NM_CHECK
FROM NUMCHECK NC
)
SELECT
(SELECT
NP.TCHAR AS [text()]
FROM NUM_PART NP
WHERE NP.NM_CHECK = 0
ORDER BY NP.N DESC
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')
;
April 8, 2014 at 8:00 am
milos.radivojevic (4/8/2014)
BWFC (4/8/2014)
That solution works but will give an error if the string consists of only numerals.Thank you BWFC for the correction!
A great thing in this forum is whenever you write something which is not completely true or in some corner cases would not work someone puts appropriate comment. That's really great and continuously improve the quality of posts and comments.
Thanks again!
You're welcome and I'd always wondered what the point of the REVERSE function was and now it makes sense. It does help if I myself check for corner cases. My original post didn't work in all cases.
'0568556506111677862713D8' is considered numeric for some reason and the whole string was returned rather than the final 8.
This fixes it though.
declare @rowstring varchar(max)
SELECT
case
when patindex('%[^0-9]%',@rowstring) = 0 then @rowstring
else
REVERSE(
SUBSTRING(REVERSE(@rowstring),PATINDEX('%[0-9]%',
REVERSE(@rowstring)),PATINDEX('%[^0-9]%',
SUBSTRING(REVERSE(@rowstring),(PATINDEX('%[0-9]%', REVERSE(@rowstring))),LEN(@rowstring)))-1))
end
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 8, 2014 at 11:37 am
Underlying code/logic:
SELECT
string,
--len(string), last_numeric_char, length_of_numeric,
SUBSTRING(string, last_numeric_char - length_of_numeric + 1, length_of_numeric)
FROM (
SELECT '123756zxfggr123456' AS string --123456
UNION ALL
SELECT '4vbz67xfggr123dfd' --123
UNION ALL
SELECT '123756zxfgg43r5' --5
UNION ALL
SELECT '123756123456475' --123756123456475
) AS test_data
CROSS APPLY (
SELECT LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_numeric_char
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_numeric_char - 1)) + '.') AS length_of_numeric
) AS ca2
Coded as function:
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
RETURN (
SELECT
SUBSTRING(string, last_numeric_char - length_of_numeric + 1, length_of_numeric)
FROM (
SELECT @strAlphaNumeric AS string
) AS param_input
CROSS APPLY (
SELECT LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_numeric_char
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_numeric_char - 1)) + '.') AS length_of_numeric
) AS ca2
)
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2014 at 9:46 am
For the set-based, tally table, CTE freaks among us, here is a set-based solution that uses a tally table and CTEs:
SET NOCOUNT ON;
-- The @Strings table variable represents whatever your source table is.
DECLARE @Strings TABLE ([StringKey] INT NOT NULL IDENTITY(1, 1),
[RawString] VARCHAR(1000) NOT NULL,
PRIMARY KEY CLUSTERED ([StringKey])
);
INSERT INTO @Strings ( [RawString] ) VALUES ( '' );
INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc' );
INSERT INTO @Strings ( [RawString] ) VALUES ( '789' );
INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789' );
INSERT INTO @Strings ( [RawString] ) VALUES ( '789abc' );
INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789abc' );
INSERT INTO @Strings ( [RawString] ) VALUES ( '123rgt456tgb789asd' );
INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789' );
INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789asd' );
-- we create 6 Common Table Expressions (CTEs)
-- The first 3 are solely for the purpose of creating a tally table of 1,000 sequential numbers.
-- If you already have a tally table defined in your database you use it in the [Characters] CTE and remove the first 3 CTEs
-- The [Characters] CTE breaks each character of each string down into its own row with a value ([IsDigit]) indicating whether it is a numeric character or not
-- The [LastNumerics] CTE finds the location of the last numeric character in each string
-- The [[LastNonNumerics]] CTE finds the location of the last non-numeric character in each string that occurs BEFORE the last numeric character
WITH [First_10]([Dummy]) 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
), -- first 10 rows
[Full_1000]([Dummy]) AS (SELECT 1 FROM [First_10] a CROSS JOIN [First_10] b CROSS JOIN [First_10] c), -- 10 x 10 x 10 = 1000 rows
[Tally_1000]([SeqNo]) AS (SELECT ROW_NUMBER() OVER (ORDER BY [Dummy]) FROM [Full_1000]),
[Characters] AS (SELECT .[StringKey], [sn].[SeqNo], ISNUMERIC(SUBSTRING(.[RawString], [sn].[SeqNo], 1)) AS [IsDigit]
FROM @Strings s INNER JOIN [Tally_1000] AS sn ON [sn].[SeqNo] <= LEN(.[RawString])
WHERE [sn].[SeqNo] <= LEN(.[RawString])
),
[LastNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNumeric]
FROM [Characters] AS c
WHERE [c].[IsDigit] = 1
GROUP BY [c].[StringKey]
),
[LastNonNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNonNumeric]
FROM [Characters] AS c INNER JOIN [LastNumerics] n ON [n].[StringKey] = [c].[StringKey]
AND [c].[SeqNo] < [n].[LastNumeric]
WHERE [c].[IsDigit] = 0
GROUP BY [c].[StringKey]
)
-- Now we can do our SELECT query to extract the characters from each string starting right after the last non-numeric character and going through the last numeric character.
SELECT .[RawString], SUBSTRING(.[RawString], ISNULL([nn].[LastNonNumeric], -1) + 1, ISNULL([n].[LastNumeric], 0) - ISNULL([nn].[LastNonNumeric], -1)) AS [Result]
FROM @Strings s LEFT OUTER JOIN [LastNumerics] n ON [n].[StringKey] = .[StringKey]
LEFT OUTER JOIN [LastNonNumerics] nn ON [nn].[StringKey] = .[StringKey];
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply