Collation problem with "like"
-
I had to change my database collation , from "Sql_Latin1_General_CP1253_greek_CI_AS" to "Sql_Latin1_General_CP1253_greek_CI_AI".
I have a problem with "like" statements. When I have zeros ("0") in varchar columns and the zeros are in the end of the value (like "120000") a like statement as "like '120%'" has no results.
If the value is "120100" then this statement "like '120%'" results the line as well.
Any clue?
Thanks a lot
-
when you changed your database collation, did you change all the existing columns too?
a temp solution would be like this:
select * from gmact where actnbr like '120%' collate Sql_Latin1_General_CP1253_greek_CI_AS
a permenant solution would be to change all the columns to the new collation; this should help:
this is a long running query; i've limited it to TOP 5 for demo purposes. change the collation to the one you are using, and change the TOP 5 when you are please with the results:
alter database dbname COLLATE SQL_Latin1_General_CP1_CI_AS
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')
ORDER BY TBLNAME,COLNAME
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE)
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')
ORDER BY TBLNAME,COLNAME
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have changed all the columns from database
statements like "select * from gmact where actnbr like '120%' collate Sql_Latin1_General_CP1253_greek_CI_AS" would be a solution , but I have to change all my sources , they include select statements.
-
i would have thought that differences in collation are what is breaking your comparisons.....but if all the columns are the same collation as the database, i would think that that is no longer an issue;
maybe someone else has an idea.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I wonder how you changed the collation of all the columns! It is not a simple operation when those are using FK, Indexes, Check Constraints, statistics, etc ...
As expressed above changing the default collation of a DB does NOT change the data!!!
* Noel
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply