Using COALESCE with LIKE - 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)

Using COALESCE with LIKE

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-02 : 17:01:18

I have the following stored procedure;


CREATE PROC ps_Serial_Equip_SELECT_Search
	@EquipID varchar(5) = NULL,
	@CustID varchar(6) = NULL,
	@Model varchar(5) = NULL,
	@Serial varchar(12) = NULL,
	@CustName varchar(25) = NULL

AS

SELECT
	Equip_ID,
	A.Customer_Number,
	Model,
	Serial,
	Name1_Cun AS 'CustName'
FROM SERIAL_EQUIP A INNER JOIN CUSTOMER_NAME B ON A.Customer_Number = B.Customer_Number
WHERE Equip_ID = COALESCE(@EquipID,Equip_ID)
	AND A.Customer_Number = COALESCE(@CustID,A.Customer_Number)
	AND Model = COALESCE(@Model,Model)
	AND SERIAL = COALESCE(@Serial,Serial)
	AND Name1_Cun LIKE '%' + COALESCE(@CustName,Name1_Cun) + '%'
ORDER BY Name1_Cun,A.Customer_Number,Model,Serial

Everything works fine unless I enter a partial string as the @CustName parameter. If I enter a complete name in this parameter
it returns the expected results.

Thanks

Edited by - TogasPoon on 04/02/2003 17:02:24

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-02 : 18:39:30

It works fine for me. See if this example proves it:

CREATE TABLE Table1 (Column1 varchar(10) NULL)

INSERT INTO Table1 VALUES('TogasPoon')

SELECT Column1
FROM Table1
WHERE Column1 LIKE '%' + COALESCE('Togas', Column1) + '%'

DROP TABLE Table1

In my example, I get the record returned. What do you get? Does my example represent what you are trying to do?

Tara

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-03 : 09:56:35

Yeah, your example does work for me. Still can't figure out why my code doesn't.

I've simplified it to try and narrow things down.
The first two examples return no results, the last two do...

DECLARE	@CustName varchar(25)

SET @CustName = '180 DEG'

SELECT Name1_Cun
FROM CUSTOMER_NAME
WHERE Name1_Cun LIKE '%' + COALESCE(@CustName,Name1_Cun) + '%'

SELECT Name1_Cun
FROM CUSTOMER_NAME
WHERE Name1_Cun LIKE '%' + COALESCE('180 DEG',Name1_Cun) + '%'

SELECT Name1_Cun
FROM CUSTOMER_NAME
WHERE Name1_Cun LIKE '%' + ISNULL(@CustName,Name1_Cun) + '%'

SELECT Name1_Cun
FROM CUSTOMER_NAME
WHERE Name1_Cun LIKE '%180 DEG%'

I've changed my stored procedure to use ISNULL and everything is working. I'm still not sure what's wrong with my original???

I'm using SQL Server 7 if it makes a difference.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-04-03 : 10:40:16

does

AND Name1_Cun LIKE COALESCE('% + @CustName + '%', Name1_Cun)
work instead of
AND Name1_Cun LIKE '%' + COALESCE(@CustName,Name1_Cun) + '%'

??

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-03 : 11:09:39

OK, found the problem

the datatype is set up as char, if i change it to varchar then the proc works fine.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-03 : 11:22:05

Ahhh! now it makes sense.

The old

"why doesn't LIKE '%' + @Var + '%' return any results?"

All the extra spaces in the char() datatype ....

I should've seen that one from a mile away!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-03 : 11:40:09

Oh, by the way:

Field1 LIKE '%' + ISNULL(@Var,Field1) + '%'

and

Field1 LIKE '%' + COALESCE(@Var,Field1) + '%'

do a little bit too much. Consider replacing both with:

Field1 LIKE ISNULL('%' + @Var + '%', '%')

That should perform bit better. Why? because now the whole ISNULL() expression is a CONSTANT, and doesn't have to be evalauted for each row in the query.

Also, what is says is: If the parameter is NULL, compare the field to LIKE '%', as opposed to LIKE '%' + fieldname + '%', which is a quicker comparison.

Hopefully that makes sense and helps a little.

- Jeff

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-03 : 11:49:20

It does and does.

Thanks Jeff!

Warren

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources