CODEWITHSUNDEEP
sqlsql-server
akhrot
akhrot

Reputation: 445

extract all value from string in sql

i am not sure if it is possible with sql. i am trying to bring all numeric value from string.

declare @mytable table
(
myvalue varchar (50)
)

insert @mytable
select 'DOBIH3HA3' UNION ALL
select 'TAARE567ZAMEEN5' UNION ALL
select 'GAG645JAMU43'

The below approach is very closed but not getting the desired output.

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(myvalue, pos, LEN(myvalue))
    FROM (
        SELECT myvalue, pos = PATINDEX('%[0-9]%', myvalue)
        FROM @mytable
    ) d
) t

Please share your expertise.... Thanks a lot

Upvotes: 0

Views: 73

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

By using an ad-hoc tally table and a Cross Apply

Select A.* 
      ,B.*
 From @mytable A
 Cross Apply (
        Select String=(Select Substring(A.myvalue,N,1) 
                       From (Select Top (Len(A.myvalue)) N=Row_Number() Over (Order By Number) From master..spt_values ) NA
                       Where  Substring(A.myvalue,N,1) Like '[0-9]'
                       For XML Path('') ) 
 ) B

Returns

myvalue         String
A2SK3HSDSK3     233
KGI6620GYUIG    6620
GAG4444BY9Y     44449

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

first we need to create function

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




create table #mytable 
(
myvalue varchar (50)
)

insert #mytable
select 'A2SK3HSDSK3' UNION ALL
select 'KGI6620GYUIG' UNION ALL
select 'GAG4444BY9Y' 

SELECT dbo.udf_GetNumeric(myvalue) 
from #mytable

    output

    233
    6620
    44
    449

Upvotes: 1

Cato
Cato

Reputation: 3701

        -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Scalar Function (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      <Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description: <Description, ,>
    -- =============================================
    ALTER FUNCTION numonly
    (
        -- Add the parameters for the function here
        @p nvarchar(max)
    )
    RETURNS nvarchar(max)
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @ResultVar nvarchar(max)

        DECLARE @L int = LEN(@P)
        WHILE @L > 0 
        BEGIN 
            SELECT  @ResultVar = SUBSTRING(@P , @L , 1) + COALESCE(@ResultVar, '') WHERE SUBSTRING(@P , @L , 1) BETWEEN '0' AND '9';
            SET @L = @L - 1;
        END

        -- Return the result of the function
        RETURN @ResultVar

    END
    GO



select dbo.numonly(myvalue) from @mytable;

Upvotes: 1

James Casey
James Casey

Reputation: 2507

CREATE FUNCTION dbo.fn_ExtractNumeric(@input VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @n INT
    SET @n = PATINDEX('%[^0-9]%', @input)
    BEGIN
        WHILE @n > 0
        BEGIN
            SET @input = STUFF(@input, @n, 1, '' )
            SET @n = PATINDEX('%[^0-9]%', @input )
        END
    END
    RETURN ISNULL(@input,0)
END
GO

declare @mytable table
(
myvalue varchar (50)
)

insert @mytable
select 'A2SK3HSDSK3' UNION ALL
select 'KGI6620GYUIG' UNION ALL
select 'GAG4444BY9Y' 

SELECT dbo.fn_ExtractNumeric(myvalue)
FROM @mytable

Upvotes: 3

Related Questions