CODEWITHSUNDEEP
mysqlregexwhitespace
user1149244
user1149244

Reputation: 755

RegEx pattern to check for whitespace at the start or end of a string

I know that \s is the pattern used to match any white space in a string. Now, my problem is what should be the exact regex pattern to check white space at the start or end of the string?

So, if I have a string " hello world", white space is at the start, so it will return true. If the string is " hello world ", it will return true too. White space is at the start and end of the string.

I don't need to remove white space but I just need to check if the data I got from the database has white space at the start or end.

This is my SQL query but this doesn't really work. This only picks up data that has white space at the end:

select * from `data_table`
where `data_content` regexp '[ \f\t\v]$';

The query above give me 7 results as there are only 7 records which have white space at the end. There are 3 records that I need to fetch which have white space at the start of the string.

I tried running this SQL query:

select * from `data_table`
where `data_content` regexp '^\s' || `data_content` regexp'\s$';

It gives me 87 results. I inspected the result one by one through PHPMYADMIN and the results I got are those data that have no white space in it.

Upvotes: 3

Views: 28991

Answers (2)

EpixRu
EpixRu

Reputation: 211

[[:space:]] is another character class that would work.

This query will find all records that have a space at the beginning or end of line.

SELECT * FROM `foo` WHERE `bar` REGEXP '(^[[:space:]]|[[:space:]]$)'

Upvotes: 0

mmdemirbas
mmdemirbas

Reputation: 9158

Using \s for whitespace caused the problem for me. So, I used [[:blank:]] instead and this works well:

select data_content as     'leading_or_trailing'
from   data_table
where  data_content regexp '^[[:blank:]]|[[:blank:]]$';

select data_content as     'leading'
from   data_table
where  data_content regexp '^[[:blank:]]';

select data_content as     'trailing'
from   data_table
where  data_content regexp '[[:blank:]]$';

Upvotes: 5

Related Questions