Extract numeric part

Question

Hi All,

I have column that has values such as 10202BFC, 10440KPMI  etc. I need to extract the numeric portion from this column so that the result is 10202, 10440. The first few characters are always going to be numeric and the last few characters are going to be alpha numeric. Any idea how to do this in a select statement?

Answer 1

Try this:

Declare @s varchar(100),@result varchar(100)
set @s='as4khd0939sdf78' 
set @result=''

select
    @result=@result+
            case when number like '[0-9]' then number else '' end from 
    (
         select substring(@s,number,1) as number from 
        (
            select number from master..spt_values 
            where type='p' and number between 1 and len(@s)
        ) as t
    ) as t 
select @result as only_numbers 

Source: http://beyondrelational.com/modules/2/blogs/70/posts/10821/extract-only-numbers-from-a-string.aspx

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 2

If the number of characters for the first portion is a constant 5:

declare @test table(a_String varchar(12));
insert into @test
select '10202BFC' union all select 10440KPMI
;
select
  a_String,
  left(a_String, 5) as the_Numbers
from @test;

If the length of the number can vary, consider using the patindex function; perhaps something like this:

select
  a_String,
  case when terminator > 0 then left(a_String, terminator - 1) else a_String
  end as the_Numbers
from @test
cross apply
( select patindex('%[^0-9]]%, a_String) as terminator
) xx

Answer 3

Hi Peace !

You might also need to look out at my Data Clean Up function;

http://gallery.technet.microsoft.com/DataCleanUp-Function-5f2d1c30

Or you may alternatively use below query;

DECLARE @Data VARCHAR(MAX),@InValidData VARCHAR(100), @CleanUpData VARCHAR(MAX)
SELECT	@Data = '123456687#*&MMNHCH', @InValidData = '[^0-9]' 

SELECT	@CleanUpData =
		CASE	WHEN PATINDEX('%' + @InValidData +  '%', @Data) > 0  
				THEN SUBSTRING(@Data, 0, PATINDEX('%' + @InValidData +  '%', @Data))
		END

SELECT	@CleanUpData AS CleanUpData

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 4

If the first few characters are always numeric and the last few characters are always alpha numeric then try below.

declare @test table(string varchar(12));
insert into @test
select '10202BFC' 
union all select '10440KPMI'
union all select '1044045KPMI'
union all select '104404KP123'
select SUBSTRING(string,1,PATINDEX('%[^0-9]%',string)-1) from @test

---

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 5

declare @test table(string varchar(12));
insert into @test
select '10202BFC' 
union all select '10440KPMI'
union all select '1044045KPMI'
union all select '104404KP123'

select	*
from	@test t
	cross apply	
	(
		select	substring(t.string, number, 1)
		from	master.dbo.spt_values x
		where	x.type	= 'P'
		and	x.number	between 1 and len(t.string)
		and	substring(t.string, number, 1) between '0' and '9'
		for	xml path('')
	) n (num)

---

K H Tan

Answer 6

Create the Following Function:

CREATE FUNCTION splitStringNumeric(
	@value AS NVARCHAR(100),
	@NumericPart AS BIT = 1
)RETURNS NVARCHAR(100)

BEGIN
	DECLARE @returnValue NVARCHAR(100)
	DECLARE @numberPart  INT;
	DECLARE @stringPart  NVARCHAR(100);
	DECLARE @cnt		 INT;

	SET @cnt = 1;
	SET @returnValue = NULL;

	WHILE @cnt < (LEN(@value)+1)
	BEGIN

		IF(ISNUMERIC(SUBSTRING(@value,@cnt,1)) = 0)
		BEGIN 
			IF @cnt =1 
			BEGIN
				BREAK;
			END;

			SET @stringPart = SUBSTRING(@value,@cnt,@cnt)
			SET @numberPart = SUBSTRING(@value,0,@cnt)

			BREAK;
		END;

		SET @cnt = @cnt+1
	END;

	IF @NumericPart = 1
	BEGIN
		SET @returnValue = @numberPart;
	END;

	ELSE
	BEGIN
		SET @returnValue = @stringPart;
	END;

	RETURN @returnValue;
END;

Then Call it when u want:

DECLARE @tbl TABLE(Val NVARCHAR(100));
INSERT INTO @tbl
SELECT '10202BFC' 
UNION ALL SELECT '10440KPMI'
UNION ALL SELECT '1044045KPMI'
UNION ALL SELECT '104404KP123'

SELECT dbo.splitStringNumeric(Val, 1) AS NumericPart,dbo.splitStringNumeric(Val, 0) AS StringcPart
FROM @tbl

Answer 7

Hi..

CREATE FUNCTION splitStringNumeric(
	@value AS NVARCHAR(100),
	@NumericPart AS BIT = 1
)RETURNS NVARCHAR(100)

BEGIN
	DECLARE @returnValue NVARCHAR(100)
	DECLARE @numberPart  INT;
	DECLARE @stringPart  NVARCHAR(100);
	DECLARE @cnt		 INT;

	SET @cnt = 1;
	SET @returnValue = NULL;

	WHILE @cnt < (LEN(@value)+1)
	BEGIN

		IF(ISNUMERIC(SUBSTRING(@value,@cnt,1)) = 0)
		BEGIN 
			IF @cnt =1 
			BEGIN
				BREAK;
			END;

			SET @stringPart = SUBSTRING(@value,@cnt,@cnt)
			SET @numberPart = SUBSTRING(@value,0,@cnt)

			BREAK;
		END;

		SET @cnt = @cnt+1
	END;

	IF @NumericPart = 1
	BEGIN
		SET @returnValue = @numberPart;
	END;

	ELSE
	BEGIN
		SET @returnValue = @stringPart;
	END;

	RETURN @returnValue;
END;

your function is working, but if data is like this given below, How to separate string and number can u guide me plzz.....

DECLARE @tbl TABLE(Val NVARCHAR(100));
INSERT INTO @tbl
SELECT 'b10202BFC' 
UNION ALL SELECT 'a10440KPMI'
UNION ALL SELECT 'b1044045KPMI'
UNION ALL SELECT 'b104404KP123'

SELECT dbo.splitStringNumeric(Val, 1) AS NumericPart,dbo.splitStringNumeric(Val, 0) AS StringcPart
FROM @tbl

Answer 8

Hi

Please try below links

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

http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/

please marks as answer if found helpful

regards

Rajeev

Answer 9

Thanks

Answer 10

Hi

According to this problem,

I have column that has values such as 10202BFC, 10440KPMI  etc. I need to extract the numeric portion from this column so that the result is 10202, 10440. The first few characters are always going to be numeric and the last few characters are going to be alpha numeric. Any idea how to do this in a select statement?

It says first few characters are always going to be numeric.

so your sample data is not in this domain. I've created this function above condition.

Answer 11

DECLARE @table TABLE
(
Id VARCHAR(50)
)

INSERT @table
        ( Id )
VALUES  
(' 10202BFC'),('10440KPMI')

SELECT SUBSTRING(id,PATINDEX('%[0-9^]%', Id), (PATINDEX('%[a-z]%',Id )-PATINDEX('%[0-9^]%', Id))) FROM @TABLE

---

Please have look on the comment

Answer 12

Try following

SELECT LEFT(col_name,PATINDEX('%[^0-9]%',col_name)-1)  FROM table_name;

Regards

amchtwe