LIKE and char(n) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LIKE and char(n)

Author

Topic

kasabb
Starting Member

8 Posts

Posted - 2007-05-07 : 11:45:39

Given that the following query can be fixed by using rtrim(@myVar), why is it that the first query works and the second on does not? The only difference is the length of @myVar. Additionally, field1 is defined as char(50) in table1.


declare @myVar char(37);
set @myVar = 'abc%';

select * from [table1] where [field1] like @myVar;


declare @myVar char(38);
set @myVar = 'abc%';

select * from [table1] where [field1] like @myVar;

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 12:16:38

its because of the way CHAR works. SQL Server creates a fixed with value from your value by padding any extra characters with spaces.
So 'abc' when declared as CHAR(38) will be converted to 'abc '. so your search queries will fail. Its a good idea to use varchar instead of char if the values in the column vary in length.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 14:40:29

quote:

declare @myVar char(38);
set @myVar = 'abc%';

select * from [table1] where [field1] like @myVar;

Also I think after using Varchar datatype this will be working.. it should be like this ..



declare @myVar char(38)  Varchar(38) ;
set @myVar = 'abc%' 'abc';

select * from [table1] where [field1] like  @myVar + '%';

Chirag

http://chirikworld.blogspot.com/

kasabb
Starting Member

8 Posts

Posted - 2007-05-08 : 14:32:19

Thanks for the input. I was looking more for why it doesn't work this way. I can fix it by either of the methods mentioned above.

For example why the behavior is different when I have char(37) than it is when I have char(38).

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-09 : 00:44:15

char is a fixed length and varchar is variable length datatype...char will be used as datatype for the column where the data entered will be almost near the length of the column ortherwise or almost varchar is the best solution

Why here it did not work...field1 column having data whose length is more than or equal to 27 so it did not work but if u increase the length it worked..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources