help with query to search for 3rd decimal

Question

help with query to search for 3rd decimal

pmosca 7795

Grasshopper

Points: 20
More actions
April 22, 2010 at 10:08 am

#219511

I am trying to search multiple tables for numbers that contain a third decimal not equal to zero.
the columns are numeric.
For example...
if the column contains
34.0200
1232.0560
234.4400
451232.123
I want to list only records 1232.0560 and 451232.123
is that possible?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

Check the charindex function to find the decimal and the substring function to get the third position after that. You'll need to convert the column to a varchar first.

WayneS SSC Guru Points: 95460 More actions · Answer 2

declare @test-2 table (col1 numeric(10,4))

insert into @test-2

SELECT 34.0200 UNION ALL

SELECT 1232.0560 UNION ALL

SELECT 234.4400 UNION ALL

SELECT 451232.123

select * from @test-2

where col1 like '%.__[1-9]%'

This does do an implicit conversion of the field to a varchar, so it won't utilize an index. But it does get you what you wanted.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

Very clever, Wayne. I like that.

Rob Schripsema
Propack, Inc.

pmosca 7795 Grasshopper Points: 20 More actions · Answer 4

thanks everyone.. this one below worked best for me..

SELECT *

FROM [TSIT1].[dbo].[gl30000]

where

SUBSTRING(SUBSTRING(CAST(orcrdamt AS VARCHAR),CHARINDEX('.',CAST(orcrdamt AS VARCHAR))+1,LEN(CAST(orcrdamt AS VARCHAR))),3,1) <> '0'