Check if varchar has non 0 values in the decimal? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check if varchar has non 0 values in the decimal?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-25 : 09:28:11

Hi All,

I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.

So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.

Please help.

Thanks,

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:36:44

Why why why do you store numeric values as VARCHAR in the first place?

SELECT * FROM SalesHistory WHERE Sales NOT LIKE '%.00'

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-25 : 10:26:30

Thanks Peso.

Thats silly of me, I never thought that since its a varchar type I can simply use the like operator :|.

Well how I can convert this Sales filed of varhchar type to 2 decimal values?

Thanks,

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:31:35

If you are 100 percent sure that ALL values

1) have decimal formatting
2) or is null

try this. this will fail if any of the values in sales column can not be interpreted as a numeric value.
also beware of records having three "decimals" or more.

CREATE TABLE #Sample (Data VARCHAR(20))

INSERT	#Sample
SELECT	'45345.6' UNION ALL
SELECT	NULL UNION ALL
SELECT	'123.00'

SELECT	*
FROM	#Sample

ALTER TABLE	#Sample
ALTER COLUMN	Data NUMERIC(14, 2)

SELECT	*
FROM	#Sample

DROP TABLE	#Sample

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-25 : 10:46:19

Thanks Peso.

I am using the following and it seem to be working...
Select sales,(convert(decimal(18, 2), [sales])) from saleshistory

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:50:21

So you are still planning to keep Sales as varchar datatype?

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-25 : 10:53:44

No I am not.
Isn't the following is converting it to decimal? Please suggest...

(convert(decimal(18, 2), [sales]))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 11:03:02

No. An yes.

You only convert the column value "on the fly", while selecting. Nothing persistent going on.
After query is run, column datatype is still varchar.

To make a persistent alteration/change, you must use code like I posted earlier.

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-25 : 11:12:55

Peso,

I understand its on the fly , but I wrote it here as an example.

Thanks for all your help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources