matching pattern

Question

Post reply

matching pattern

sql_2005_fan

SSCommitted

Points: 1871
More actions
February 15, 2007 at 8:50 am

#153436

I have a Stored procedure which has a input parameter that accepts a string with mulpile words('alaska,newyok,ohio'). It can have N number of words.
IN the procedure I need to check a column that match all these values.

Say If i have coulmn states in the database as 'alaska,michigan,ohio,newyork' and 'alaska,newyok,ohio'

When I search with the above input string'alaska,newyok,ohio') My Storedprocedure has to return both the above strings.
If the input string is ''alaska,michigan,ohio,newyork' it should return only the first string.

I am not searching exactly with the input string. I will split it into indivudual words and i iwll check states column which has all that words.

How can i implement this.

Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

search for the Split function in the scripts section of SSC; there are several that will do this.

searching for Split in the forums will get you loads of ewxamples as well:

Discussion	Date	Search Score
Process streamlining ideas	2/14/2006 7:54:00 PM	100
Help with Split Proc	6/27/2006 3:20:00 PM	99.15
RE: AND/OR problems...	6/2/2005 12:47:00 PM	97.88
RE: Select Exclusivly IN	6/23/2005 6:20:00 AM	97.88
Importing UK 2001 Census Data into SQL Server	6/1/2004 6:50:00 AM	97.46
RE: Extract Rows for Fields that match all the search criteria	4/4/2005 9:32:00 AM	97.46
Split a field into multiple records	10/7/2006 11:29:00 AM	97.46
RE: Split a field into multiple records	10/11/2006 8:21:00 PM	97.46
RE: Split a String value into rows	12/31/2002 3:05:00 AM	97.03
How to write a recursive query?	12/23/2004 10:25:00 AM	97.03
RE: Parsing Data By "\" and "/" Characters	5/19/2005 7:14:00 AM	97.03
RE: optimization x crash query	10/31/2005 5:45:00 AM	97.03
RE: bulk insert text file into two tables	4/23/2006 12:02:00 PM	97.03
RE: Convert a list of values from VARCHAR to INT	9/18/2006 10:21:00 PM	97.03
RE: In using a parameter in a stored procedure	6/8/2005 2:23:00 PM	96.61
RE: pass results table from a function as argument to another	10/27/2005 11:08:00 AM	96.61
RE: Split function input be column of a table	2/9/2007 9:18:00 AM	96.61
RE: Calling procedure over and over.... can''''t work it.	3/10/2006 6:52:00 AM	96.19
Combine Data and Split into separate txt files for each row grouping	3/15/2006 7:02:00 PM	96.19
RE: aggregate functions using time intervals	10/4/2006 2:37:00 PM	96.19
More Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bnordberg SSCertifiable Points: 5480 More actions · Answer 2

I normally pass the values to a string to table function (I can send you one if you need it just message me). This creates a temp table with each value now being a row. Then do an innner join (or whatever) to your other tables to limit the resultset, or get other values ...