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

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

mdcarr

ASKER

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

ASKER CERTIFIED SOLUTION

LordOfPorts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdcarr

ASKER

Dear Mr. LordOfPorts,

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

Thank you very very much,
Michael

mdcarr

ASKER

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