SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.
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
/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';
GO
As result of above script self explainary, I will be not describing it in detail. If you use any alternative method to do the same task, please let me know.
Reference : Pinal Dave (https://blog.sqlauthority.com)
34 Comments. Leave new
thanks, for me will be useful
hi pinal…..
with the help of this we can not remove whole alphabets in our string it removes only first group occurence of alphabets e.g. ‘qwert12354asd34as’ the result of this string will be 12354asd34as as per requirment output should be 1235434………..it need one more while loop…
nitin
Nitin: I posted an alternate solution here that does what you need, but Mr. Dave has not bothered to approve the comment.
Trying again:
http://sql blog.com/blogs/adammachanic/archive/2006/07/12/patternbasedreplacementudf.aspx
Hi Nitin,
The above logic is giving the required result.
I think you might have missed somewhere. Please recheck it at your end.
Thanks,
Nizam
How does this accomodate decimals? Like this:
‘Price: 3.56783’
I would just want to return 3.56783
Hello Andrew,
In the function dbo.udf_GetNumeric replace the line:
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
with following line at two places:
SET @intAlpha = PATINDEX(‘%[^0-9.]%’, @strAlphaNumeric)
Regards,
Pinal Dave
it’s perfect but what about other language like Arabic
Another method
Madhivanan
Hello sir
My senario like this , I have one text box and one table called student(Name,Roll_No) and one Insert button to insert data in DB. If i type “amit-28” in textbox then by clicking on Insert button “amit” should inserted into Name field and “28” should inserted into RollNo field. So what code to write??or query ??
Thanking You
You can easily use split function in the application
dim sp
sp=split(string,”-“)
Now you can access first and second values by
sp(0) and sp(1)
This really worked well,thanks
To handle both decimal and non-decimal numbers I’ve created this procedure to handle e.g. ‘ABC355,88ghf’ to 355,88 and ‘ABC 78 QQ’ to 78,00 etc:
CREATE PROCEDURE [dbo].[CleanDataFromAlpha]
@alpha varchar(50),
@decimal decimal(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMsg varchar(50)
DECLARE @Pos int
DECLARE @CommaPos int
DECLARE @ZeroExists int
DECLARE @alphaReverse varchar(50)
DECLARE @NumPos int
DECLARE @Len int
— 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
— 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX(‘%[0-9]%’, @alphaReverse)
— 3 Get the lenght of the string
SET @Len = LEN(@alpha)
— 4 Add a comma after the numeric data in case it’s no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len – @NumPos + 1))
+ ‘,’
+ SUBSTRING(@alpha, (@Len – @NumPos + 2), 50)
— Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
— if it’s 0 after the handling, else we set @decimal to NULL
— If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( ‘0’ , @alpha ,1 )
— Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX(‘%,%’, @alpha)
IF (@CommaPos = ”) BEGIN
SET @CommaPos = 20
END
SET @Pos = PATINDEX(‘%[^0-9]%’,@alpha)
— Replaces any aplha with ‘0’ since we otherwice can’t keep track of where the decimal
— should be put in. We assume the numeric number has no aplhe inside. The regular way
— to solve this is to replace with ”, but then we miss the way to find the place to
— put in the decimal.
WHILE (@Pos > 0) BEGIN
SET @alpha = STUFF(@alpha, @pos, 1, ‘0’)
SET @Pos = PATINDEX(‘%[^0-9]%’,@alpha)
END
IF (@alpha IS NOT NULL AND @alpha != ”) BEGIN
SET @decimal = convert(decimal(14, 5), substring(@alpha, 1, (@CommaPos – 1))
+ ‘.’
+ substring(@alpha, (@CommaPos + 1), 20))
END
— Since we in this case don’t want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET @decimal = NULL
END
END
/ Christofer
Thanks a lot for your function..
You are fantastic!!!! saved me tons of work!
Thank alot
Thanks, But if I use it on 1 lakh rows with 5 numerical column data it takes more than 10 min. so please help me to resolve it.
Hi,
My issue is somehow related to this one. I want to extract last 10 digits from a float column
I have values such as 12343456678
and I want to extract the last 10 digits only (2343456678). I haven’t had success googling this for a while now.
Please remember the column I have has all numeric data and is chosen to be float.
Really save a lot of work
Product 000770420 DELL E420-A0 42″ 1080P LED LCD HDTV P=12
Product 000770470 DELL E470I-A0 47″ 1080P LED LCD HDTV P=7
Product 000770701 DELL E701I-A3 70″ CLASS 1080P LED SMART TV P=4
Product 000791339 DELL E390I-A1 39″ 1080P SMART LED LCD P=28
The above is the sample data. I just need to extract the first set of numbers like
000770420
000770470
000770701
000791339
How do I do this?
Thank you Dave. for me It works without any issue :)
Helloo,
I Have function that return string value like ‘(4+2)*(3-1)’
Then how to compute this value?? as math .
I need result as : 12
Please help me….
thanks in advance…. pls pls