Please help me - SQL Server Forums

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)

Please help me

Author

Topic

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-23 : 00:44:21

Hi ,

i need a help. when i used this below query, i am getting wrong result
See the output, it differs from the condition.

In my project prty_price field is varchar and it accepts the input like 34,8790 340000 678.89 34343 $56,454.44 like this so i used Cast process to check the condition in my query now, the result i am getting is wrong see the condition i have given minimum value 200000 maximum value 400000

SELECT prty_Price FROM tbl_Property WHERE cast(replace(replace(replace(prty_Price,'$',''),',',''),'.','') as int)>= '200000' AND (cast(replace(replace(replace(prty_Price,'$',''),',',''),'.','') as int)<= '400000') AND prty_Deleted=0

output
prty_Price

3456.78 it should not come
34,3467
200000
234544
345345
345346
3454.45 it should not come

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 00:48:51

thats because you're removing decimal point and then casting to char. what you need is this modification to get your output

SELECT prty_Price FROM tbl_Property WHERE round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)>= 200000 AND round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)<= 400000) AND prty_Deleted=0

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-23 : 00:54:36

quote:
Originally posted by visakh16

thats because you're removing decimal point and then casting to char. what you need is this modification to get your output

SELECT prty_Price FROM tbl_Property WHERE round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)>= 200000 AND round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)<= 400000) AND prty_Deleted=0

Kindly expalin me with exact brackets where to put and i am confused with that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 00:58:30

quote:
Originally posted by Ambikaa

quote:
Originally posted by visakh16

thats because you're removing decimal point and then casting to char. what you need is this modification to get your output

SELECT prty_Price FROM tbl_Property WHERE round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)>= 200000 AND round(cast(replace(replace(prty_Price,'$',''),',','') as decimal(20,2)),0)<= 400000) AND prty_Deleted=0

Kindly expalin me with exact brackets where to put and i am confused with that

what i've done is remove the outermost replace from your query

SELECT prty_Price FROM tbl_Property WHERE cast(~~replace(~~replace(replace(prty_Price,'$',''),',','')~~,'.','')~~ as int)>= '200000' AND cast(~~replace(~~replace(replace(prty_Price,'$',''),',','')~~,'.','')~~ as int)<= '400000') AND prty_Deleted=0

then changed casting to decimal(20,2) and applied round() over it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources