T sql query to verify decimal exist in the column

Question

Post reply

T sql query to verify decimal exist in the column

ry.sqldev

SSC Eights!

Points: 882
More actions
November 21, 2011 at 8:41 am

#250792

Hi

How to verify whether a decimal point exist in the column data.

eg:I have a Column called primaryCode

the all the rows in this column should have decimal.

appriciate any suggestion

Thanks

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

November 21, 2011 at 8:42 am

#1410774

SELECT * FROM dbo.Table WHERE Col LIKE '%.%'

Or if it's already a decimal columns =>

SELECT * FROM dbo.Table WHERE Col <> FLOOR(Col).

GSquared SSC Guru Points: 260824 More actions · Answer 2

What data type is the column?

Does "1.0000" qualify as having a decimal point in it? Or does it need to have a non-zero value after the decimal to be valid for your needs?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

CELKO (11/21/2011)

If the4 column is DECIMAL(s,p) the question makes no sense. Ergo, this is a CHAR(n) or VARCHAR(n) and you did not tell us which. You do this with a CHECK() constraint, something like this:

foobar CHAR(7) DEFAULT '000.000' NOT NULL

CHECK (foobar LIKE'[0-9][0-9][0-9].[0-9][0-9][0-9]')

SQL is a declarative data language, so write your code that way.

Agreed, but we get some really weird questions once in a while. Makes no sense only applies when you understand the basics which, as you may know, is not always the case!