Clever Geek Handbook

Space character SQL LIKE

let's say I have a string where words are separated by 1 or more spaces and I want to use that string and the SQL LIKE condition. How can I make my SQL and say it matches 1 or more space characters in my string? Is there a SQL wildcard I can use to do this?

Tell me

+3


source to share


7 replies


If you just want to get anything you want, at least one space / space, you can do something like the following WHERE myField LIKE '% %'



+4


source


If your dialect permits it, use SIMILAR TO, which allows for more flexible matching, including the normal regex quantifiers '?', '*' And '+', with the grouping indicated by '()'

where entry SIMILAR TO 'hello +there'

will match 'hello there'

with any number of spaces between the two words.



I think in MySQL it is

where entry RLIKE 'hello +there'
+3


source


http://www.techonthenet.com/sql/like.php

The patterns you can choose are as follows:

% allows you to match any string of any length (including zero length)

_ allows you to match one character

+2


source


you cannot do it with LIKE, but what can you do if you know that this condition may exist in your data, since you are inserting data into the table, use a regex to detect it in front and set a flag on another column created for this purpose.

+2


source


Another way of mapping for one or more spaces would be to use []

. And so it was.

LIKE '%[ ]%'

This will match one or more spaces.

+2


source


I just replace the whitespace characters with "%" . Let's say I want to make a LIKE query on a line like this "I want to query this line with LIKE" 

@search_string = 'I want to query this string with a LIKE'
@search_string = ("%"+@search_string+"%").tr(" ", "%")
@my_query = MyTable.find(:all, :conditions => ['my_column LIKE ?', @search_string])

first I add '%' to the beginning and end of the line with

("%" + @SEARCH_STRING + "%")

and then replace the rest of the remaining spaces with '%' characters , for example

.tr ("", "%")

+2


source


I know this is already late, but I never found a solution for this in relation to the question LIKE

.

There is no way to do what you want in SQL LIKE

. You will need to use REGEXP

it [[:space:]]

inside your expression as well.

So, to find one or more spaces between two words ..

WHERE col REGEXP 'firstword[[:space:]]+secondword'
+1


source






More articles:


All Articles