Can't Compare Numeric Characters

[mdwg2020]

Quick Question,

I am attempting to run a query. In the query I am testing for a 1 being placed in a particular column. Now this column is of type nvarchar.

I tried to do a simple query like, ColumnName = '1', and that doesn't return anything. I also attempted to do ColumnName = N'1' and that didn't work either.

Any suggestions on what will pick up that 1?

Thanks,

Brooks

 

[Katy44]

Try like '%1%' - if it has spaces on either side, you could use the trim function on your columnname.

 

[mdwg2020]

Yeah I tried that too, am not sure why it isn't picking it up. It understands the NULL, because I did a test for that, and it worked fine, but for some reason it doesn't understand my '1'. I also attempted to do the N'1', because I read about it somewhere with unicode and what not, and that didn't work either.

Is there anyway I could convert the one to character data, and then compare it using a character code? Or any other way I can pick it up?

Thanks,

Brooks

 

[rsshetty]

did you try columnName = 1 (without quotes)?

rsshetty.
It's always in the details.

 

[Katy44]

what happens if you do

select 'Value :' + columnname

Do the 1s show up?

 

[mdwg2020]

OK, now this is strange, I altered the query slightly to the same ColumnName = '1', and it actually pulls them all up, but what I want to do, is get everything where it doesn't equal '1'. So I tried ColumnName <> '1', and then it doesn't return anything, however it should return around 800 records. I also tried the other format != '1', and that didn't work either.

Why would it understand = '1', but not <> '1' ?

Thanks,

Brooks

 

[Katy44]

what is the option when it's not 1? It's not null is it?

 

[SQLBill]

What happens if you run this:

SELECT DISTINCT ColumnName,
COUNT(ColumnName)
FROM tablename
GROUP BY ColumnName
ORDER BY ColumnName ASC

What values are returned?

-SQLBill

Posting advice: FAQ481-4875

 

[MDXer]

have you tried LTrim(RTrim(ColumnName)) = '1'

with the column being an nvarchar leading or trailing spaces may exist.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[DrAardvark]

I would do a convert on it

Select convert(decimal(9,2),Column)

The only issue you have is then if it is blank. It doesn't like that. I did find a work around with convert(money, column). This will treat blanks, spaces or zero length fields as a zero. Then you can do maths functions like <>

 

[SQLBill]

DrAardvark,

<> is NOT a math function. It means NOT EQUAL.

A <> B

It's only a COMPARISON function.

-SQLBill

Posting advice: FAQ481-4875

 

[DrAardvark]

OK that is a little picky. < or > would you be happy with? If you really want to be good about data integrity then set the field to be BIT with a default of 0 and then test it as a 1 or 0. The advantage of a convert if you are going to use a number in a text field is that it deals with zero length dtrings, numbers and NULLs. If you want to do a yes/no field why not use a CHAR(1) field Y and N. SQL does not get so confused then when you ask it to find a string (which is a number) in a string field

 

[SQLBill]

I wasn't being picky. The poster isn't doing Greater Than or Less Than. Brooks is trying to figure out why a String (NVARCHAR) comparison isn't working. No math. Just comparing one string to another string. In this case, the string just happens to be a number.

That's why I asked the poster to run:

SELECT DISTINCT ColumnName,
COUNT(ColumnName)
FROM tablename
GROUP BY ColumnName
ORDER BY ColumnName ASC

It will give us an idea of what other values are in the column and why the Not Equal To comparison isn't working.

-SQLBill

Posting advice: FAQ481-4875

 

[mdwg2020]

Sorry guys, been on vacation. I am going to go back and attempt several of the things that you said to attempt.

Katy44 said this: what is the option when it's not 1? It's not null is it?

And sometimes it is null, so I don't know if that means anything, or where she was heading with this.

I know that some of the other values are date values, so I can't really do any conversions. Some of the values are null, and some of the values are whatever the person decided to put into the field. It is simply an optional field and every business area uses it slightly different.

 

[Katy44]

if you try (columnname is null or columnname <>1)
what do you get?

 

[mdwg2020]

Whenever I do a <>1 I receive nothing as a result. That is were the problem is. However if I do =1, and picks all of them up correctly. It doesn't make since that there would be a difference does it?

 

[SQLBill]

Are all of the rest of the values NULL?

This makes a BIG difference. NULL means UNKNOWN. It means "I don't know if this value is a 1 or not".

This statement is false: NULL <> 1

SQL Server doesn't know if NULL is 1 or not. So if your values are only 1 or NULL, then nothing will be returned by <>1.

That's why we are asking you what are the values you have in that column. If you run the following query, it should show us what distinct values you have in that column:

SELECT DISTINCT ColumnName,
COUNT(ColumnName)
FROM tablename
GROUP BY ColumnName
ORDER BY ColumnName ASC

-SQLBill

Posting advice: FAQ481-4875

 

[ProdAir]

you can also try CHARINDEX('1', ISNULL(columnname, '')) = 0