help with exact match

Question

help with exact match

paul 17028

SSC Rookie

Points: 41
More actions
May 3, 2015 at 9:16 am

#296550

Hi
I am trying to alter this SQL to search for exact match only, non case sensitive. I have been trying for over an hour without result
Thank you
strSQL = "SELECT N_image_number, N_image_name, N_image_desc, N_available, N_i_size, N_image_th, N_image " _
& "FROM tblart " _
& "WHERE N_image_desc LIKE '% " & Replace(strSearch, "'", "''") & "%' " _
& "OR N_image_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR N_image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
& "ORDER BY N_image_order ASC;"

Viewing 15 posts - 1 through 15 (of 15 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

paul 17028 (5/3/2015)
Hi
I am trying to alter this SQL to search for exact match only, non case sensitive. I have been trying for over an hour without result
Thank you
strSQL = "SELECT N_image_number, N_image_name, N_image_desc, N_available, N_i_size, N_image_th, N_image " _
& "FROM tblart " _
& "WHERE N_image_desc LIKE '% " & Replace(strSearch, "'", "''") & "%' " _
& "OR N_image_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR N_image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
& "ORDER BY N_image_order ASC;"

Quick question, can you post the ddl ( create table) and sample data in the form of an insert statement?

😎

paul 17028 SSC Rookie Points: 41 More actions · Answer 2

Thank you Eirikur for your reply, but your request for more information is way over my head. I am using an access database as a gallery for my work and I was hoping to remove the wildcard operators from my WHERE statement. At the moment when I search for keyword "figure" I also return "non figure" records

Thankyou

Paul m

djj SSCoach Points: 18831 More actions · Answer 3

Are you able to get examples of the data you are searching?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

paul 17028 (5/3/2015)
Thank you Eirikur for your reply, but your request for more information is way over my head. I am using an access database as a gallery for my work and I was hoping to remove the wildcard operators from my WHERE statement. At the moment when I search for keyword "figure" I also return "non figure" records
Thankyou
Paul m

Have you tried simply removing the wildcards? They look like this: %

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jim-S SSCommitted Points: 1823 More actions · Answer 5

Hi Paul,

Try this (untested) code...

strSQL = "SELECT N_image_number, N_image_name, N_image_desc, N_available, N_i_size, N_image_th, N_image " _

& "FROM tblart " _

& "WHERE N_image_desc = '" & Replace(strSearch, "'", "''") & "'" _

& "OR N_image_name = '" & Replace(strSearch, "'", "''") & "'" _

& "OR N_image_number = '" & Replace(strSearch, "'", "''") & "'" _

& "ORDER BY N_image_order ASC;"

EDIT* I missed out the ampersands

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

Quick thought, no need for a dynamic sql statement here.

😎

The query you are constructing looks like this

SELECT

N_image_number

,N_image_name

,N_image_desc

,N_available

,N_i_size

,N_image_th

,N_image

FROM tblart

WHERE N_image_desc LIKE '%strSearch%'

OR N_image_name LIKE '%strSearch%'

OR N_image_number LIKE 'strSearch'

ORDER BY N_image_order ASC;

Adding a @strSearch parameter it looks like this

DECLARE @strSearch VARCHAR(100) = '';

SELECT

N_image_number

,N_image_name

,N_image_desc

,N_available

,N_i_size

,N_image_th

,N_image

FROM tblart

WHERE N_image_desc LIKE '%' + @strSearch + '%'

OR N_image_name LIKE '%' + @strSearch + '%'

OR N_image_number LIKE @strSearch

ORDER BY N_image_order ASC;

Jim-S SSCommitted Points: 1823 More actions · Answer 7

Eirikur Eiriksson (5/5/2015)
Quick thought, no need for a dynamic sql statement here.
😎
The query you are constructing looks like this
SELECT
N_image_number
,N_image_name
,N_image_desc
,N_available
,N_i_size
,N_image_th
,N_image
FROM tblart
WHERE N_image_desc LIKE '%strSearch%'
OR N_image_name LIKE '%strSearch%'
OR N_image_number LIKE 'strSearch'
ORDER BY N_image_order ASC;
Adding a @strSearch parameter it looks like this
DECLARE @strSearch VARCHAR(100) = '';
SELECT
N_image_number
,N_image_name
,N_image_desc
,N_available
,N_i_size
,N_image_th
,N_image
FROM tblart
WHERE N_image_desc LIKE '%' + @strSearch + '%'
OR N_image_name LIKE '%' + @strSearch + '%'
OR N_image_number LIKE @strSearch
ORDER BY N_image_order ASC;

Hi Eirikur, The OP is using an access database, he has just posted in the wrong place :hehe:.

paul 17028 SSC Rookie Points: 41 More actions · Answer 8

Hi

Thanks for replies. removing the wildcards was the first thing I tried but this resulted in "no records found" which is strange because i have other statements without the wildcards and they work fine.

I have been looking around and I have found this "collate Latin1_General_CI_AS" but I have bean unable to implement it if it is at all possible.

Could it be the way i am adding the data to the N_image_desc field in access? I have it with a comma and a space like this. I have noticed that I have some spaces before the comma in some cases would this make a difference?

woman, woman , beach , sea , seaside , figure, figurative , dress , romantic , coast , waves , sun , sunshine

Thankyou

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

paul 17028 (5/6/2015)
Hi
Thanks for replies. removing the wildcards was the first thing I tried but this resulted in "no records found" which is strange because i have other statements without the wildcards and they work fine.
I have been looking around and I have found this "collate Latin1_General_CI_AS" but I have bean unable to implement it if it is at all possible.
Could it be the way i am adding the data to the N_image_desc field in access? I have it with a comma and a space like this. I have noticed that I have some spaces before the comma in some cases would this make a difference?
woman, woman , beach , sea , seaside , figure, figurative , dress , romantic , coast , waves , sun , sunshine
Thankyou

Wait a minute. Are you saying that one of your columns contains comma delimited values? That is why you would need the wildcards both leading and trailing. It is also why you are going to get false matches like you found. This is because you have violated 1NF by putting multiple values in a single table intersection. I don't know what to tell you that might work with Access other than to move these values to a properly normalized table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

paul 17028 SSC Rookie Points: 41 More actions · Answer 10

Thanks for your help.

So to normalise the table do I need to remove the commas? What about spaces? and is it possible for instance to have "non figure" as an entry and "figure" as a separate entry? at the moment records for "non figure" are also displayed when I search for "figure".

Thank you

Paul M

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

paul 17028 (5/6/2015)
Thanks for your help.
So to normalise the table do I need to remove the commas? What about spaces? and is it possible for instance to have "non figure" as an entry and "figure" as a separate entry? at the moment records for "non figure" are also displayed when I search for "figure".
Thank you
Paul M

No it isn't about having commas in your data, it is about having more than 1 value in a single intersection. What you currently have as a column with a bunch of values most likely needs to be split off into its own table with a row for each value.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

In Access you would create a new table similar to something like this.

create table ImageTags

(

ImageNumber number

, TagDescription varchar

)

Then for your example of "woman , beach , sea , seaside , figure, figurative , dress , romantic , coast , waves , sun , sunshine" you would have 12 rows in this new table to hold all the tags for you image.

To get the data out of it you just join to this new table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

paul 17028 SSC Rookie Points: 41 More actions · Answer 13

Thank you

it's all making sense now. fields are only supposed to have one entry.

Thank you all again

Paul M

dale_berta Ten Centuries Points: 1323 More actions · Answer 14

paul 17028 (5/6/2015)
Hi
... I have been looking around and I have found this "collate Latin1_General_CI_AS" but I have bean unable to implement it if it is at all possible. ...

Your original post said you wanted not case sensitive, which is the default for SQL character comparisons. The use of COLLATE will make it case sensitive, so for this query you won't want it.

That being said, the syntax is simply to add it after the character value, as in [font="Courier New"]taba.col1 COLLATE Latin1_General_CI_AS[/font]. You can use it with char, varchar, text, nchar, nvarchar, or ntext data types. You can use it anywhere in the query where you would have a character field or column (column list, conditions in CASE, WHERE, JOIN ON), GROUP and ORDER BY, ...). Also note there are other collation sequences, which change the sort order for conventions in other languages.