Problem with LIKE Clause Wildcards

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

General SQL Server Forums

New to SQL Server Programming

Problem with LIKE Clause Wildcards

Author

Topic

maikens
Starting Member

6 Posts

Posted - 2010-11-21 : 06:28:31

Hi all, I am new to this forum, so I apologize in advance for any broken rules and such.

I am working on a T-SQL assignment for college, and I am having trouble matching a particular pattern using the LIKE clause with wildcards. The purpose of the statement is to format a smalldatetime field for specific output (ie, Mar 15th, 2010)

My script is:


SELECT Bug.Name,
CASE
WHEN Bug.[Date] LIKE '%-[0-9]1 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'st' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN Bug.[Date] LIKE '%-[0-9]2 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'nd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN Bug.[Date] LIKE '%-[0-9]3 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'rd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
ELSE
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'th' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
END 
FROM Software
INNER JOIN Bug
ON Software.SoftwareID = Bug.SoftwareID

It always ends up going to ELSE, so I get output such as: Nov 21th, 2010. What am I missing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 07:18:42

try using PATINDEX then. like


...
CASE
WHEN PATINDEX('%-[0-9]1 %',Bug.[Date]) >0 THEN

...

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

maikens
Starting Member

6 Posts

Posted - 2010-11-21 : 07:57:57

Hmm...tried that. A problem similar to that which I ran up against with SUBSTRING: it won't let me use a smalldatetime as the first parameter- guess it wants a string.

maikens
Starting Member

6 Posts

Posted - 2010-11-21 : 08:02:29

Ok, so I used CONVERT to get around that problem. But PATINDEX still tests as 0, so same result as with LIKE...

I think the problem lies within the actual pattern I constructed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 08:11:52

how are your date values coming after the CONVERT? whats the style value used?

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

maikens
Starting Member

6 Posts

Posted - 2010-11-21 : 09:02:08

Here is my code with PATINDEX used instead of LIKE


SELECT Bug.Name,
CASE
WHEN PATINDEX('%-[0-9]1 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'st' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN PATINDEX('%-[0-9]2 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'nd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN PATINDEX('%-[0-9]3 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'rd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
ELSE
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'th' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
END
FROM Software
INNER JOIN Bug
ON Software.SoftwareID = Bug.SoftwareID
ORDER BY Bug.[Date] DESC;
GO

It still goes to the else.
Does that answer your question?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 12:23:52

wat about this?


SELECT Bug.Name,
CASE
WHEN PATINDEX('%[0-9]1 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'st' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN PATINDEX('%[0-9]2 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'nd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN PATINDEX('%[0-9]3 %',CONVERT(VARCHAR(15), Bug.[Date])) > 0 THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'rd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
ELSE
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'th' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
END
FROM Software
INNER JOIN Bug
ON Software.SoftwareID = Bug.SoftwareID
ORDER BY Bug.[Date] DESC;
GO

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

maikens
Starting Member

6 Posts

Posted - 2010-11-21 : 14:43:02

Yeah I tried that as well. It works better, but for some reason it only works in some instances. It correctly formats dates for November 21st, and February 22nd, but not for 'October 03th' or 'July 1th'

maikens
Starting Member

6 Posts

Posted - 2010-11-23 : 23:52:35

Hi all. Just in case anyone was interested, I figured out the problem. Since Adaptive Server converts the smalldatetime to style 107 (ie: Jan 1, 2010 9:00AM), and I was assuming it looked something like 2010-01-01 00:09:00, my LIKE pattern wouldn't match for single digit dates. So I just added a space in the clause. The select statement that worked for me is:


SELECT Bug.Name,
CASE
WHEN Bug.[Date] LIKE '%[0-3 ]1 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'st' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN Bug.[Date] LIKE '%[0-3 ]2 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'nd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
WHEN Bug.[Date] LIKE '%[0-3 ]3 %' THEN
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'rd' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
ELSE
	SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),1,6) + 'th' + SUBSTRING(CONVERT(VARCHAR(15),Bug.[Date],107),7,6)
END
FROM Software
INNER JOIN Bug
ON Software.SoftwareID = Bug.SoftwareID
ORDER BY Bug.Name, Bug.[Date] DESC;
GO

thanks to visakh16 for helping me with this one!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:19:53

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources