Search for one string inside another.

Question

Search for one string inside another.

michael.barnard

SSC Rookie

Points: 32
More actions
June 9, 2009 at 7:02 am

#210351

Hi all.
I am VERY new to SQL and the little I think I know is self taught. I have a task to search for a string within a string but I'm not getting it.
I work for a UK council. We have a database with graffiti tags and I need to be able to search for one tag within a string of others. The database is created by an external company on a SQL server (unknown version) and sent to us. Here I look at it in Mapinfo Pro 9.5. Don't ask why, I just do!
In the database I have a field called Tag. It is made of 60 characters. These are some imaginary completed fields:
1. TOM FRED ABC
2. AB
3. SILLY STRING
4. ABSOLUTE
5. QUIRK FRED JOHN IAM JOHN
6. TOM AB
7. AB IAM
I need to search for instances of a string within the strings above. Example, search for the tag "AB". This should bring up rows 2,6 and 7, not row 1 where AB is a part of ABC.
In Mapinfo Pro I can search using a box titled SQL Select. In this box I can choose the table to examine etc, and add an SQL condition. This is where I need help.
I have been trying conditions such as:
Tag like "AB"
...but This only picks up a row where AB is alone, row 2 in the example above.
Tag like "AB%"
...this shows lines where AB is the start as well as the only word, rows 2 and 7 above.
So, can anyone point me to a tutorial, guide or whatever that will explain how to put conditions together to get what I want please? Or maybe even show me here? I know this is bread and butter stuff for you, but it's new to me.
Thanks in advance.
Mike.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i believe you might be looking for the behavior of a full text index.

With a full text index, you can do WHERE CONTAINS(columnname,'AB'), and it only does "whole word" matching, and if it a word, would do variations of the word, like CONTAINS(columnname,'market') would find the whole words marketer,marketing,markets, and not partial latches.

adding the fulltext index is easy, and would do what you want.

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!

michael.barnard SSC Rookie Points: 32 More actions · Answer 2

Hi Lowell, and thanks.

The problem is that from in MapinfoPro I can only use this "condition" box to do my search. Mapinfo has it's own language, mapbasic, but I have never used it and don't know it. However it seems to use standard SQL conditions so I was hoping to learn about these.

I tried the term you describe* and unfortunately got an error. "Found [DB] while searching for [)]"> I assume that Mapinfo has a different syntax.

*where contains(Tag "DB")

I'm trying:

Tag Like "xx" or Tag like "% xx %" or whatever

...at the moment. Again, thanks.

justanewone Ten Centuries Points: 1398 More actions · Answer 3

Hi Michael,

You are better off with using an OR to choose the criteria as you suggested. The code below will identify any Tag fields with the exact 'AB', 'AB' with a space both sides or 'AB' with a space with side. This will identify any off these Tags fields seperated by a space and therefore not part of a individual string within the data. (provided they are all make unique by space!). Should work in Mapview but not sure.

WHERE (Tag = 'AB' OR Tag Like '% AB %' OR Tag Like 'AB %' OR Tag Like '% AB')

Regards,

Dave

michael.barnard SSC Rookie Points: 32 More actions · Answer 4

justanewone (6/9/2009)
Hi Michael,
You are better off with using an OR to choose the criteria as you suggested.
WHERE (Tag = 'AB' OR Tag Like '% AB %' OR Tag Like 'AB %' OR Tag Like '% AB')

Hi Dave.

I tried your example but the "Where" isn't understood. So I just left theconditions bare and my first test pulled up the correct results. Wahoo. Good to know that I was on the right path, but just hadn't yet gotten all the possible selections.

Now I need to learn mapbasic and write some scripts to speed up things!

Thank you.;-)

brian clements-470670 Mr or Mrs. 500 Points: 521 More actions · Answer 5

Hi Michael,

I know you already got your answer but I tried this statement and it worked just as well.

select *

from "Table name"

where tag like '% _b'

or tag like 'a_ %'

or tag like 'ab'

michael.barnard SSC Rookie Points: 32 More actions · Answer 6

Hi..

Are you a Mapinfo user then? Hey, I'm not alone after all! I'm trying to teach myself mapbasic just now. I've got some course notes from a friend who did a course 2 years ago and the help files. It could take some time!

Anyway, thanks for the extra way of asking my query. But, as I'm a newbie, what's the underscore mean as a part of it? % means wildcard. ! means wildcharacter... but an underscore?

Thanks.

Mike.