How to find out numeric number

# How to find out numeric number

• Thursday, December 06, 2012 6:54 AM

hi!

I have a field in the database where the values are like

hhjjj12345hhhkk

Now, i have to convert them as

12345

all character remove here

please help any query remove character in all column mixed number or character

### All Replies

• Thursday, December 06, 2012 7:02 AM

Try the below:

```DECLARE @Sample TABLE(string VARCHAR(20));
INSERT INTO @Sample
SELECT 'hhjjj12345hhhkk' UNION ALL
SELECT '-7655' UNION ALL
SELECT 'asd5-5dffgdfg105';
WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
FinalOutput AS (
SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
THEN SUBSTRING(string, n, 1)
ELSE ''
END + ''
FROM Nums
WHERE n <= LEN(string)
FOR XML PATH('')) AS stringout
FROM @Sample)
SELECT string, stringout FROM FinalOutput; ```

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

• Thursday, December 06, 2012 7:05 AM

```declare @str varchar(20)
set @str = 'hhjjj12345hhhkk'
select @str = SUBSTRING(@str,patindex('%[0-9]%',@str),LEN(@str))
select @str = SUBSTRING(@str,0,PATINDEX('%[^0-9]%',@str))
select @str```

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

• Thursday, December 06, 2012 7:14 AM

```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(N```
SOURCE TAKEN FROM BELOW LINK

http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

• Thursday, December 06, 2012 7:20 AM

DECLARE
@str CHAR(20),
@arr VARCHAR(100),
@chr CHAR(2),
@index INT
SET @str = 'Abjs4567kdZ'
SET @chr = substring(@str,1,1)
SET @index = 1
SET @arr = ''
WHILE len(@chr) > 0
BEGIN
IF NOT ((ascii(@chr) >= 97 AND ascii(@chr) <= 122) OR ((ascii(@chr) >= 65 AND ascii(@chr) <= 90)))
BEGIN
SET @arr = @arr + rtrim(@chr)
END
SET @chr = substring(@str,1,1)
SET @str = substring(@str,2,len(@str))
END
SELECT @arr

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

• Thursday, December 06, 2012 7:26 AM

hi!

I have a field in the database where the values are like

hhjjj12345hhhkk

Now, i have to convert them as

12345

all character remove here

please help any query remove character in all column mixed number or character

DECLARE
@str CHAR(20),
@arr VARCHAR(100),
@chr CHAR(2),
@index INT
SET @str = 'Ab\$%\$js44567kdZ'
SET @chr = substring(@str,1,1)
SET @index = 1
SET @arr = ''
WHILE len(@chr) > 0
BEGIN
IF (ascii(@chr) >= 48 AND ascii(@chr) <= 57)
BEGIN
SET @arr = @arr + rtrim(@chr)
END
SET @chr = substring(@str,1,1)
SET @str = substring(@str,2,len(@str))
END
SELECT @arr

Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

• Thursday, December 06, 2012 1:29 PM

As you're seeing, there are many ways to do it.  If your situation is always the relatively predictable way you listed (some letters, then the number all together, then more letters), you could probably get away with a less robust approach, but if you want something that can handle almost any situation, you'll need a more robust solution.

Anyway, here's yet another way to approach it, a recursive CTE.

```		Declare @Demo Table (DID int identity, name varchar(99))
Insert @Demo select 'hhjjj12345hhhkk' UNION select 'a1b2c3d4ee55fff666gggg7777h' UNION select 'Al1' UNION Select '2Bob\$' Union Select '!Carl%' UNION Select '%D#<>?:"{}|\][+_)(*&^	%\$#@!~`_+?>a*vi()(d&*(&*#< >?:"{}|\][J+o_n)es(*&^%\$#@!~`_+?><,./;:"' UNION Select '    Eddie     '

Declare @Pattern varchar(999) /* Pick one of the  patterns, or make your own */
--Set @Pattern = '%[^ abcdefghijklmnopqrstuvwxyz0123456789-'']%' -- Non-alphanumeric
--Set @Pattern = '%[^abcdefghijklmnopqrstuvwxyz]%' -- Alphabetic only
Set @Pattern = '%[^0123456789]%' -- Numeric only

;With Cleaner (DID, PassNo,  Name, OName) as
(   /* Start here - Incorporate the column to clean and the table */
Select DID
, 1 as PassNo
, Name  /* Column to clean here */
, Name as OName
From @Demo D  /* From table here */
UNION ALL
Select DID
, Passno + 1
, Cast( Replace(C.Name, SubString(C.Name, CAB.BadPos, 1), '') as VarChar(99)) -- CAN.Name
, OName
From Cleaner C
Cross apply (Select Patindex(@Pattern, Name) as BadPos ) as CAB
where CAB.badpos > 0
)
, Cleaned as
(
Select Name, Oname  from Cleaner
Where  Patindex(@Pattern, Name) = 0
)

Select D.*, Cleaned.Name as Cleaned_Name, Ltrim(Rtrim(Cleaned.Name)) as Trimmed_and_Cleaned
from @DEMO D
Inner join CLEANED on CLEANED.Oname = D.Name
```

Above is "probably" faster than a function or using XML, but partly depends on the distribution of characters vs. numbers. table size, etc.  Here's a "Translate" function, that operates mostly similarly to the way Oracle's translate built-in function works.  To call, "select dbo.translate('xxyyzz12345xxyyzz', 'abcdefghijklmnopqrstuvwxyz', ''), but can also be used for other translating purposes too.

```CREATE Function [dbo].[Translate] ( @InpStr as varchar(8000), @Fromlist as VarChar(8000), @IntoList as varchar(8000))
Returns VarChar(8000)
as
BEGIN
/*
Translate: Recreate "translate" feature as commonly implemented in several languages.
Does not validate.  Shorter "Into" parm replaces corresponding "From" characters to empty string
Author: JohnQFlorida
*/
Declare @Loop int
Set @Loop = 1

While @Loop <= Len(@FromList)
Begin
Set @InpStr = Replace(@InpStr, SubString(@FromList, @Loop, 1), SubString(@IntoList, @Loop, 1))
Set @Loop = @Loop + 1
End

Return @InpStr
End;```