Is there a better way to constuct this reverse Wildcard Select statement

Question

Is there a better way to constuct this reverse Wildcard Select statement

mpdillon

Hall of Fame

Points: 3566
More actions
May 13, 2014 at 9:19 am

#307936

I am trying to find all the patterns that match a specific item number. This is the reverse of what I usually do, find All the Item Numbers that match a specific pattern. In this case the patterns are stored in the database and the Item number is the variable.
The item numbers (11 Characters) in this query have "significant" digits, i.e. when a specific digit appears at a particular character space, something can be done. Perhaps it helps build up the item description or it might determine if the whole item number appears in a list or not.
As such I am required to do a lot of pattern matching. Currently, I am storing combinations of wildcards and digits in a field. When I need to determine which records could potentially be used for a specific item number, I use the query:
Select ItemSpec, Doc from AWildcards
where '123-456-789' like Itemspec
I am not used to placing a field to the right of the operator and the value I need to match on the left. This works but is there a better way to do this?
If I know the item number, is there a better way to match it to the stored patterns?
Here is the code to build a sample table and populate it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AWildcards](
[ItemSpec] [varchar](15) NULL,
[Doc] [varchar](500) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into AWildcards
Select '1__-___-___','Desc1'
union all
Select '2__-___-___','Desc2'
union all
Select '___-111-___','Desc3'
union all
Select '___-___-1__','Desc4'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Looks reasonable to me. I don't see a "better" way.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

mpdillon (5/13/2014)
I am trying to find all the patterns that match a specific item number. This is the reverse of what I usually do, find All the Item Numbers that match a specific pattern. In this case the patterns are stored in the database and the Item number is the variable.
The item numbers (11 Characters) in this query have "significant" digits, i.e. when a specific digit appears at a particular character space, something can be done. Perhaps it helps build up the item description or it might determine if the whole item number appears in a list or not.
As such I am required to do a lot of pattern matching. Currently, I am storing combinations of wildcards and digits in a field. When I need to determine which records could potentially be used for a specific item number, I use the query:
Select ItemSpec, Doc from AWildcards
where '123-456-789' like Itemspec
I am not used to placing a field to the right of the operator and the value I need to match on the left. This works but is there a better way to do this?
If I know the item number, is there a better way to match it to the stored patterns?
Here is the code to build a sample table and populate it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AWildcards](
[ItemSpec] [varchar](15) NULL,
[Doc] [varchar](500) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into AWildcards
Select '1__-___-___','Desc1'
union all
Select '2__-___-___','Desc2'
union all
Select '___-111-___','Desc3'
union all
Select '___-___-1__','Desc4'

If you don't want anything but digits in the places you have marked for wildcard characters, consider replacing each underscore with [0-9] including the brackets.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mpdillon Hall of Fame Points: 3566 More actions · Answer 3

Scott, Thanks for the confirmation.

Jeff, I tried the [0-9] and it worked. I am not sure if there is a performance gain. This query will not be used very often and the table will only contain a few thousand records. So I will stay with the "_" as it yields the same results. Thank you.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 4

mpdillon (5/14/2014)
Scott, Thanks for the confirmation.
Jeff, I tried the [0-9] and it worked. I am not sure if there is a performance gain. This query will not be used very often and the table will only contain a few thousand records. So I will stay with the "_" as it yields the same results. Thank you.

I wasn't aiming at a performance gain. I was aiming at data validation. The underscores will accept ANY character. Ostensibly, the "fields" that you have are supposed to be numeric only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mpdillon Hall of Fame Points: 3566 More actions · Answer 5

Jeff,

Thanks for the clarification. I appreciate it.