Avatar of mdcarr
mdcarr asked on

How do I fix this Sql select statement ?

Hello,

First thank you for your time.

My table is of the form:
Column1:  Column Name=Id, Data Type=int
Column2:  Column Name=Balance, Data Type=nvarchar(20)

The data in the table looks like this:
Row1:  1, $15,000.00
Row2:  2, $2.00
Row3:  3, -$15.45

When I execute this query:
select * from Clients where Balance > '$15'
 
I get the following result set returned:
Row1:  1, $15,000.00
Row2:  2, $2.00
Row3:  3, -$15.45

I was obviously only expecting to see Row1.

What is going on here?  I would like to use the nvarchar data type.  Is there a way to alter the structure of the query to get the proper result?  Or must I use the Money data type?

Thank you for your time.
Michael
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
mdcarr

8/22/2022 - Mon
LordOfPorts

Try the SQL statement in the code snippet below. The value must first be converted into a numeric type because comparing strings is different than comparing numbers. Before the Balance, in this case, can be converted, the $ symbol must be removed then we cast the value as MONEY data type. Note there are no quotes around 15 or whatever value you are comparing against. You might however consider separating the format from the content and go with a numeric data type instead of nvarchar if you have to perform calculations later on.
SELECT Id, Balance
FROM Clients
WHERE (CAST(REPLACE(Balance, '$', '') AS MONEY) > 15)

Open in new window

Tom

Try select * from Clients where Balance > 15
ASKER
mdcarr

Hello Mr. LordOfPorts,

That was an excellent answer!  It was quick too.

One more question if you don't mind.  Why don't I have to remove the commas?

Thank you very much,
Michael
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
LordOfPorts

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mdcarr

Dear Mr. LordOfPorts,

Fast, clear and accurate answers.  You are amazing!  I would hire you in a microsecond.

Thank you very very much,
Michael
ASKER
mdcarr

I will say the same thing here as I did in the final posting of the question just to make certain you receive the complement.  You have provided fast, clear and accurate answers.  I am impressed with you.  Do you happen to know vb6 and/or vb.net?  Within the next six months I will be looking for excellent software engineers.  Keep in touch.  mdcarr@sbcglobal.net

Thank you again Mr. LordOfPorts,
Michael Carr