Learn how to a build a cloud-first strategyRegister Now


TSQL Help2

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-08-13
I need a query that will pull all the rows where column2 has a number that ends in 09.. How?

For example if the number is 1309 I want it or 9909 etc..
Question by:cheryl9063

Assisted Solution

sshah254 earned 200 total points
ID: 36965127
select * from table where right(convert(int, column2), 2) = "09"

should work

LVL 25

Expert Comment

ID: 36966272
convert(int ... is done if it's not an int so there is no problem with decimal precision
but right(....,2)  = "09" is a char-function  
so an exctra convert(varchar...)  is needed to be able to take the last 2 positions and compare it with "09"

select * from table where right(convert(varchar,convert(int, column2)), 2) = "09"

Expert Comment

ID: 36966632
This will work in both condition if column is int or nvarchar

select * from TableName where <ColumnName> like '%09'

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 25

Assisted Solution

jogos earned 400 total points
ID: 36966836
The like '%09' is a more readable version of  the Right() = '09'  (I worked further on the given solution including the ""-error where it must be '')

But using different data-types without explicitly converting them is fooling the one who has to maintain that  sql in future. If you read that you directly assume that column is a varchar or nvarchar.  
LVL 32

Expert Comment

by:Brendt Hess
ID: 36970304
This handles most data types and sizes without any problems

FROM MyTable
WHERE Cast(Column2 as varchar(max)) LIKE '%09'

If we know that the value is numeric, this can be changed to

FROM MyTable
WHERE Cast(Column2 as varchar(32)) LIKE '%09'
LVL 32

Accepted Solution

Brendt Hess earned 1200 total points
ID: 36970370
Now - one more case.  If you also must check if the column is a number - that is, if the column is textual and may contain numbers - a validation check will need to be added to the process.

This may be what the Convert(int, column2) suggested by sshah254 was intended to do, but this will generate errors if the data is not numeric, bigger than an Int, etc.  Instead, a LIKE and IsNumeric() check will validate for this.

    SELECT *
    FROM MyTable m
    WHERE m.Column2 NOT LIKE '%![0-9. ]%'   -- If the column does not contain anything but digits, decimal, and space
        AND m.Column2 LIKE '%09'  -- the column matches the pattern needed
     ) src
WHERE IsNumeric(Src.Column2)=1  -- the column is actually numeric
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 36971943
Assuming numeric than:
FROM YourTableName
WHERE column2 % 100 = 9
LVL 70

Expert Comment

by:Scott Pletcher
ID: 36980250
[Using % also requires that the column is an integer (of any size).]

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question