LIKE with WHERE

[crxcte1]

The below works but only on the first number. The table has the column data type as nvarchar size 13.

the sql:

this works
select a, b, c from table where c like '6%';

but this does not
select a, b, c from table where c like '6625%';

There are 13 numbers in the field.

Any ideas?

 

[JamesLean]

Do you mean it's not returning the expected results? Or it is throwing an error?

I see nothing wrong with the queries. The first should return values beginning with 6, eg '6124', '6', '6479234'.

The second should return values beginning with 6625, eg '6625', '662579130', '66259811247'.

--James

 

[crxcte1]

It returns nothing. I've tried using in an asp and also in the enterprise manager query. like '6%' works fine but where I add another number, '6626%' it returns nothing.

 

[JamesLean]

This works fine for me:

CREATE TABLE #t (c1 nvarchar(13))

INSERT #t VALUES ('698781')
INSERT #t VALUES ('662598787')
INSERT #t VALUES ('66974')

SELECT * FROM #t
WHERE c1 LIKE '6%'

SELECT * FROM #t
WHERE c1 LIKE '6625%'

DROP TABLE #t

--James

 

[crxcte1]

I give, I tried changing the data type to number and float but the results are the same, when I say "WHERE C LIKE '66%'" I get no data on the return, when I type "WHERE C LIKE '6%'" I get all the data. The data is like 6625.... I never get an error just no data.

 

[BillDHS]

What happens if you use a single character to the remaining 13 instead of the %?

i.e. LIKE '66???????????'

 

[fluteplr]

What database are you using?

 

[crxcte1]

Tried "Like '66???????????'" and it returned no results, no errors, also tried "Like '66???????????%'" no results, no errors but "Like '6%' works fine.

I'm using SQL2K STD Edition

 

[fluteplr]

I just loaded this up on my copy of sql server 2000 which is running SP3 it works just fine.

A few thoughts.

1. There is something wron with your sql server install.
2. Are you using a wierd coalation order that may have a bug.
3. Have you installed any of the service packs.
4. What are you defining it as a nvarchar? If you are not going to be storing unicode data a plain varchar should work just fine.

 

[crxcte1]

Was able to format the query like the following and it worked;

SELECT *
FROM table
WHERE (colC LIKE '6%6%2%5%0%')

the return of numbers started with 66250....

Not sure what the coalation is for the master db but for my table the coalation setting is blank and can not be edited.

SP3 installed, tried varchar, float, and other data types but no results returned using "Like '66%'" format.

 

[fluteplr]

That is really weird.

Are you absolutly sure you changed the field type to varchar and still had the same result.

what you are posting would make sense for a nvarchar but not a varchar.

I would try creating a totally new test table with the field defined as varchar and giving this another try. The results you are getting to not seem to match the results anyone else is getting.

You might also want to post this in the MS SQL Server forum, to see if any users that are more SQL Server oriented have seen this issue.

 

[crxcte1]

Created another table with a single columum of data type varchar size 13 with all numbers. Ran the following query;

"select Col from tblNew where Col like '66%'" no data, no error on return

"select Col from tblNew where Col like '6%6%2%'" returned data starting 662.....

Thanks for the help, I'll checkout the MS SQL Server Forum.