How to find out numeric number

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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;