Clever Geek Handbook

How can I find odd number numbers in MS SQL?

The problem is I need to ignore the stray letters in numbers: eg. 19A or B417

0


source to share


5 answers


Take a look here: Retrieving Numbers with SQL Server



There are a few hidden gotchas that are well documented in this article.

+6


source


It depends on how much data you have in mind, but doing this in SQL is likely to be slow. Not everyone will agree with me here, but I think that all data processing should be done in the application code.



I would just take the lines I want and filter them in the application you are dealing with.

+1


source


The easiest way to do this is to create a CLR function that takes an address. In a CLR function, you must take the first part of the address (assuming it is a house number), which must be separated by spaces.

Then replace any non-numeric characters with an empty string.

You should have a string representing an integer at this point, which you can pass to the Parse method in the Int32 class to create an integer, which you can then check to determine if it is odd.

I recommend the CLR function (assuming you are using SQL Server 2005 and above and can set the database compatibility level) because it is easier to perform string manipulations in .NET than in T-SQL.

0


source


Assuming [Address] is a column with an address in it ...

  Select Case Cast(Substring(Reverse(Address), PatIndex('%[0-9]%',
             Reverse(Address)), 1) as Integer) % 2 
        When 0 Then 'Even' 
        When 1 Then 'Odd' End
  From Table
0


source


I've already done this workout. The best alternative is to add a column to the table or a join auxiliary table that stores the inferred numeric value for this purpose. Then use iterative queries to set the column multiple times until you get sufficient precision and coverage. You will come across things like "First, Third", "451a", "1200 South 19th Blvd East" and worse.

Then filter new and edited entries as they arise.

As usual, UDF should be avoided as being slow and (relatively) less debuggable.

0


source






More articles:


All Articles