Getting numbers from character column in T-SQL

Question

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

Could you explain the purpose of this change? If you want to preserve the a ll-letters records then your new column should still be a nvarchar column with the same problems (?) of the current column — Steve, Jun 26 '16 at 17:53
Thank you for your interest. It is just a client request, it's not so logical. They wanted to order the report by CleanColumn (as numbers), but many strings didn't have numbers in the UniqueId column, so they had to include strings as well. — alenan2013, Jun 27 '16 at 11:07
And so the whole purpose of sorting can be ditched. Probably a better approach is to fill the column with zero in front of each number and strings and then do the sorting. You can get some ideas from this question: [Formatting Numbers by padding with leading zeros in SQL Server](https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server/9520749#9520749) — Steve, Jun 27 '16 at 11:25

Rahul · Answer 1 · 2016-06-26T18:39:52.077

1

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);
            }
        }
    }

edited Jun 26 '16 at 18:39

answered Jun 26 '16 at 18:14

Rahul

76,197
13
71
125

Thank you very much. So, I work in ASP.NET MVC C# project with Entity Framework. Do you know how to update "CleanColumn"and UniqueId (instead of T-SQL) in Entity Framework in the same time? – alenan2013 Jun 27 '16 at 10:52
@alenan2013, be it EF/MVC it's C# code and so you can wrap the posted answer code in a method and call that method. About how to update cleancolumn column in your DB table using EF ... that's a separate question and so would suggest you to post another question/thread for that. – Rahul Jun 27 '16 at 12:24

John Cappelletti · Accepted Answer · 2016-06-27T15:51:55.560

1

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)

edited Jun 27 '16 at 15:51

answered Jun 26 '16 at 18:43

John Cappelletti

79,615
7
44
66

Thank you very much, it works great. Could you please change the code a little bit to avoid leading zeros? 622008081 instead of 0622008081? – alenan2013 Jun 27 '16 at 10:36
Updated to remove leading zeros – John Cappelletti Jun 27 '16 at 12:26
Thank you again :) I am sorry I found a message 'Conversion failed when converting the varchar value 'Ematol OPBG' to data type int' when I have tried `Select [dbo].[udfNumbersOnly]('Ematol OPBG')`. For `Select [dbo].[udfNumbersOnly]('102-2009-1008')` I have a message 'The conversion of the varchar value '10220091008' overflowed an int column.' – alenan2013 Jun 27 '16 at 15:38
I updated the answer to include one more cast to varchar this should do the trick – John Cappelletti Jun 27 '16 at 15:45
Hold on 1 second. I never imagined such a long numeric string. update in one minute – John Cappelletti Jun 27 '16 at 15:50
OK, rather than convert to int to remove the leading zeros, I convert to decimal(20,0). You could push that up IF you think the number would be more than 20 digits. – John Cappelletti Jun 27 '16 at 15:53
Update is avail for test – John Cappelletti Jun 27 '16 at 15:53
I shot myself in the foot, when I added the logic to strip leading zeros. Apologies, lack of imagination on my part – John Cappelletti Jun 27 '16 at 15:54
Glad to know. Just goes to show you, testing on REAL data is the ultimate test. Cheers – John Cappelletti Jun 27 '16 at 16:01

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

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.

edited May 23 '17 at 10:28

Community

1
1

answered Jun 26 '16 at 18:15

Yosi Dahari

6,794
5
24
44

Thank you for your interest. I will try. – alenan2013 Jun 27 '16 at 10:38

Getting numbers from character column in T-SQL

3 Answers3