CODEWITHSUNDEEP
c#sql-server
alenan2013
alenan2013

Reputation: 207

Getting numbers from character column in T-SQL

I work with SQL Server. In my database I have a column UniqueId which is nvarchar, and contains strings with characters and numbers. I want to create another column (CleanColumn) getting only numbers from the column UniqueId, and if there aren't numbers using the whole string. Also I should lose leading zeros from column UniqueId.

Is it possible in T-SQL? Or I have to use C# for that?

The column UniqueId contains for example: : 

A01943486
102-2009-1008
A-146
0622008081A
Ematol OPBG
Yavuz1098083
Yeter1391671
YKM
Zabit1446123
Zekerya13128
Zeynep1045201

Upvotes: 2

Views: 154

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

Here is q quick UDF which will return numbers only or the original string

ALTER FUNCTION [dbo].[udfNumbersOnly](@String varchar(250))
Returns Varchar(250)
As
Begin
    Declare @RetVal varchar(250) = @String
    ;with cteChar as (Select Cnt=1,Str=Char(1) Union All Select Cnt=B.Cnt+1,Str=Char(B.Cnt+1) From cteChar as B Where B.Cnt <= 255)
    Select @RetVal = Replace(@RetVal,Str,'') From cteChar where str not like '[0-9]' Option (maxrecursion 256)
    Return case when IsNull(@RetVal,'')='' then @String else cast(cast(@RetVal as decimal(20,0)) as varchar(250)) end
END

Select [dbo].[udfNumbersOnly]('0622008081A')   -- Returns 0622008081
Select [dbo].[udfNumbersOnly]('YKM')           -- Returns YKM

So in your case

Update YourTableName Set CleanColumn = [dbo].[udfNumbersOnly](UniqueId)

Upvotes: 1

Rahul
Rahul

Reputation: 77866

You can use a regular expression /[0-9]+/ (OR) \d+. A sample code given below (using C#)

    static void Main(string[] args)
    {
        List<string> str = new List<string>() { "A01943486", "102-2009-1008", "A-146", "0622008081A", 
                                                "Ematol OPBG", "Yavuz1098083", "Yeter1391671"};
        List<int> extractedNumbers = new List<int>();

        Regex reg = new Regex("[0-9]+");

        foreach (var item in str)
        {
            Match m = reg.Match(item);
            if (m.Success)
            {
                int num = Convert.ToInt32(m.Value.TrimStart('0'));
                extractedNumbers.Add(num);
            }
        }
    }

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 6999

With a disclaimer that this design seems strange and problematic, Yes - it's possible:

You can find the first number in the string then check if cast is possible and then use a case statement to actually cast it.

Upvotes: 0

Related Questions