Help with Query

[VonFranzken]

My SQL looks like this:

SELECT *
FROM dbo.table
WHERE name LIKE '%MMColParam%'

Variables:
Name: MMColParam
Default valuer: 1
Run-time value: Request.QueryString("txt_name")

Lets say there are 4 records in the table

1. Jack Black
2. Jack Black wins the gold
3. Jack Black Interview
4. Welcome Jack Black

Now my page is submitting a url paramter from a link assigned to a recordset.

If I search for Jack Black I get all the results.

But if I search for Welcome Jack Black, I only get the one and not the other valid Jack Black finds.

Thanks for your help on this.

Steve

 

[amrita418]

Well that is expected behaivour. Like returns all rows that contain the pattern.

where % refers to 0 or more of any characters. (wild card)

For example when you search for '%Jack Black%' all the four records contain this pattern.

But, when you look for '%Welcome Jack Black%' there is only one record that contains this pattern, hence you see only one record in the output.

Also, for what it is worth, the query you posted above will print all rows that contain the pattern '%MMColParam%'

Regards,
AA

 

[VonFranzken]

Is there a way to perhaps have it work both ways?'

Still confused do you think this can be accomplished?

Thanks
Steve

 

[amrita418]

What do mean by "work both ways"?

In both the cases it returns all rows that match the a given pattern, which means its upto you to set the variable to get the expected result set.

Regards,
AA

 

[VonFranzken]

My example which I have been testing metioned above only
will return all records if I search for Jack Black, however if I search for Welcome Jack Black it only finds the one.
I was searching for a way where it would be "smart" enough to find all Jack Black records.

Do you think this can be done? Im sorry for my learning curve here.

Greatly appreciative
Steve

 

[amrita418]

You are explicitly stating in your query that you want all rows that contain the literal "Welcome Jack Black" somewhere in the column so why do you expect it to return rows that donot contain the literal?

Maybe you should remove 'welcome' from MMColParam before running the sql.

Generic solution to remove the first word from the pattern i.e If you pass in 'Welcome Jack Black' get me 'Jack Black'.

select MMColParam = substring(MMColParam, charindex(MMColParam, ' ') + 1, len(MMColParam))

Then run your original query and see if that works for you.

Regards
AA

 

[VonFranzken]

SELECT *
FROM dbo.table
WHERE name LIKE select MMColParam = substring(MMColParam, charindex(MMColParam, ' ') + 1, len(MMColParam))

Is this correct?

 

[amrita418]

Declare
@MMColParam varchar(50)
set @MMColParam = 'Welcome Jack Black'

SELECT *
FROM dbo.table
WHERE name LIKE '%' + (select substring(@MMColParam, charindex(@MMColParam, ' ') + 1, len(@MMColParam))) + '%'

Regards,
AA

PS: Remember all parameters in sql server need a @before them.

Run this on sql server and let me know.

Regards,
AA

 

[VonFranzken]

This is great almost there but I guess my last question is how would I pass the @MMColParam from withing lets say dreamweaver

 

[VonFranzken]

Also I noticed if the value in the database is Jack Black
and I do @MMColParam = 'Jack Black Wins' it does not find Jack Black and I need it to.

 

[amrita418]

I would not know how to pass @MMColParam from within dreamweaver. Sorry about that.

The reason it does not return 'Jack Black' is in step one we are saying if the variable is set to 'Jack Black Wins' then using substring

(select substring(@MMColParam, charindex(@MMColParam, ' ') + 1, len(@MMColParam)))

would return 'Black Wins'

In step two we are asking sql to return all rows which have '%Black Wins%' in the name field. Given that "Jack Black" does not have Black wins in it, you do not see it in the output.

As I mentioned in my earlier response, the solution of adding select substring ....
would trunc the first field for all given MMColParam.

Are you ok with looking up all rows that match just the second word?? (i.e. If the variable is set to 'Welcome Jack Black', I want all rows that have the word 'Jack' in it).

Regards,
AA