Help with COALESCE Function - 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 2005 Forums

Transact-SQL (2005)

Help with COALESCE Function

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 07:19:11

Hi all, I am not sure if I have got the logic correct for this, I am trying to get the first match in the product table against the artistname then if that is null do a wildcard search then move on to the albumname if that is null. What I have just seems to be returning everything that matches and not the first match:

ALTER PROCEDURE [dbo].[ProductMatch]
	-- Add the parameters for the stored procedure here
	@SearchTerm VARCHAR(100) = NULL
AS
BEGIN

	SET NOCOUNT ON;
	SELECT TOP 1 id, ArtistName, AlbumName, Price, Image, URL, ReleaseDate, SubCategory, InStock FROM Products
	WHERE
	ArtistName = COALESCE(@SearchTerm,ArtistName) OR
	ArtistName like COALESCE('%' + @SearchTerm + '%',ArtistName) OR
	AlbumName = COALESCE(@SearchTerm,AlbumName) OR
	AlbumName like COALESCE('%' + @SearchTerm + '%',AlbumName)
	ORDER BY ReleaseDate DESC



END

Thanks

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 07:28:56

Will this serve the purpose?


ALTER PROCEDURE [dbo].[ProductMatch]
	-- Add the parameters for the stored procedure here
	@SearchTerm VARCHAR(100) = NULL
AS
BEGIN

	SET NOCOUNT ON;
	SELECT TOP 1 id, ArtistName, AlbumName, Price, Image, URL, ReleaseDate, SubCategory, InStock FROM Products
	WHERE
	ArtistName like COALESCE('%' + @SearchTerm + '%',ArtistName) OR
	AlbumName like COALESCE('%' + @SearchTerm + '%',AlbumName)
	ORDER BY CASE WHEN ArtistName like COALESCE('%' + @SearchTerm + '%',ArtistName) THEN LEN(REPLACE(ArtistName,@SearchTerm,'')) 
         ELSE LEN(REPLACE(AlbumName,@SearchTerm,''))
         END ASC,
        ReleaseDate DESC



END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 07:30:24

You will not get the first entry that matches your SearchTerm - you will get the first of all retrieved records ORDER BY ReleaseDate DESC from all matches.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 07:31:17

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 07:42:35

Possible optimisation to the WHERE clause - might it make a difference?


WHERE
	@SearchTerm IS NULL
	OR ArtistName like '%' + @SearchTerm + '%'
	OR AlbumName like '%' + @SearchTerm + '%'

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 08:38:21

Hi Visakh I tried your solution but it is not ordering by ReleaseDate, I am trying to get the newest first.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 08:41:32

quote:
Originally posted by webfred

You will not get the first entry that matches your SearchTerm - you will get the first of all retrieved records ORDER BY ReleaseDate DESC from all matches.

Yes I think that is what I said in my question

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 08:46:29

Then maybe you should union the different select...where statements giving it a prio and then select from derived table with min(prio)...

edit: OR you can do ORDER BY CASE WHEN ...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 08:53:46

quote:
Originally posted by Cowboy

Hi Visakh I tried your solution but it is not ordering by ReleaseDate, I am trying to get the newest first.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

then put it other way around


..
ORDER BY ReleaseDate DESC,
CASE WHEN ArtistName like COALESCE('%' + @SearchTerm + '%',ArtistName) THEN LEN(REPLACE(ArtistName,@SearchTerm,'')) 
         ELSE LEN(REPLACE(AlbumName,@SearchTerm,''))
         END ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 08:56:47

[code]
ORDER BY CASE WHEN ArtistName like '%' + @SearchTerm + '%' THEN 1
WHEN AlbumName like '%' + @SearchTerm + '%' THEN 2
ELSE 3
END ASC,
ReleaseDate DESC
[/code]

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 09:26:57

I tried both your solutions (Kristen, Visakh) but they both return all the records. What I am trying to do is do an exact match on the first if it matches then stop and return the result otherwise try the next match(wildcard). With your logic it will return the wildcard search as the first result.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:30:01

quote:
Originally posted by Cowboy

I tried both your solutions (Kristen, Visakh) but they both return all the records. What I am trying to do is do an exact match on the first if it matches then stop and return the result otherwise try the next match(wildcard). With your logic it will return the wildcard search as the first result.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

but arent you taking TOP 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 09:35:36

quote:
but arent you taking TOP 1?

Yes I was testing what else it was bringing back because it wasn't bringing back the expected result.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:39:34

so you actual need only the exact matches if present (all of them) or pattern matches(all of them)? is that your exact reqmnt?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 09:44:35

The priority is this:

ArtistName
ArtistName Wildcard
AlbumName
AlbumName Wildcard

but I only want the next item to be searched if there was no result from the previous one.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:49:35

[code]
ALTER PROCEDURE [dbo].[ProductMatch]
-- Add the parameters for the stored procedure here
@SearchTerm VARCHAR(100) = NULL
AS
BEGIN

SET NOCOUNT ON;
SELECT TOP 1 WITH TIES reqd columns...
FROM
(
SELECT TOP 1 id, ArtistName, AlbumName, Price, Image, URL, ReleaseDate, SubCategory, InStock,
CASE WHEN ArtistName = COALESCE(@SearchTerm,ArtistName) THEN 1
WHEN ArtistName LIKE COALESCE('%' + @SearchTerm + '%',ArtistName)
THEN 2
WHEN AlbumName = COALESCE(@SearchTerm,AlbumName) THEN 3
WHEN AlbumName LIKE COALESCE('%' + @SearchTerm + '%',AlbumName)
THEN 4
END AS Weight
FROM Products
WHERE
ArtistName like COALESCE('%' + @SearchTerm + '%',ArtistName) OR
AlbumName like COALESCE('%' + @SearchTerm + '%',AlbumName)
)t
ORDER BY ReleaseDate DESC,Weight DESC

END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 09:56:16

Thanks Visakh but when I tried that I got a match on the AlbumName first.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:58:20

quote:
Originally posted by Cowboy

Thanks Visakh but when I tried that I got a match on the AlbumName first.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Ah my bad make ORDER BY as

ORDER BY ReleaseDate DESC,Weight ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 10:08:01

It's still matching the AlbumName and returning when there is a match in the ArtistName first

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 10:23:31

Can you show some sample data from Products table and show us what your expected output should look like

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2010-02-18 : 10:38:05

Ok:

1,Hirsch-pinkas Piano Duo,Rochberg: Piano Music 1,2010-02-01 00:00:00.000
2,Pink,Funhouse,2009-11-02 00:00:00.000
3,Band (Theband),Music From The Big Pink: Remastered,2000-09-11 00:00:00.000

In that example if you searched for 'pink' it would return the third record when it should return the second one because if the artisname matches exactly then it should stop searching.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources