How to query a record in SQL that contains space in it.(Trailing Space) - Unleash-Coding-Skills

Sunday, 10 March 2019

How to query a record in SQL that contains space in it.(Trailing Space)


How to query records based on column values that contain trailing spaces


Suppose if we insert some records in SQL that contains spaces in column values(or specific column). Consider  we insert records that contain a column "avenger".(consider  the table name as marvels)

INSERT INTO `marvels` (name) VALUES ('Iron Man');  
INSERT INTO `marvels` (name) VALUES ('Iron Man');
INSERT INTO `marvels` (name) VALUES ('Iron Man ');
INSERT INTO `marvels` (name) VALUES ('Iron Man ');

Now you can find that there are two values with trailing spaces in it. But when we query like this we get the result as.

Select * from `marvels` where avenger="Iron Man"

The result is :

id   avenger
1    Iron Man
2    Iron Man
3    Iron Man
4    Iron Man

Now the result gives records "Iron Man" that contains trailing spaces also in it.
In case if repeat the same query with avenger = "Iron Man "

So in case if you want the exactly the records that contain the trailing spaces you need query as follows:

Select * from `marvels` where (avenger REGEXP 'Iron Man[[:blank:]]$') 

So the output of this query is:

id   avenger
3    Iron Man
4    Iron Man

So in case if you want to add another space for the query you can add more space you can add [[:blank]] in the query string. Here "$" indicates the end of the string.

If you remove the "$" symbol you will get all records with different no. of trailing space characters at the end. You can query as of your requirement.

There is also another way to query the same as follows:

select * from `marvels` where (avenger REGEXP 'Iron Man ') and length(avenger)=9

Output is:

id   avenger
3    Iron Man
4    Iron Man

As per your requirement, you can change the length and query string of the column.

Happy Coding........

Tags:
Querying SQL record with trailing space, Space at end of the column in mysql, mysql 5.6,5.5 trailing space issue, trailing space record query mysql, MYSQL, mysql

6 comments:

  1. I see some amazingly important and kept up to length of your strength searching for in your on the site360DigiTMG data science course

    ReplyDelete
  2. I think this is the minimum value to earn more than just commit
    artificial intelligence course in noida








    ReplyDelete
  3. I irrefutably valuing every single bit of it I have you bookmarked to take a gander at new stuff you post.
    what is hrdf

    ReplyDelete

  4. Somebody Sometimes with visits your blog normally and prescribed it as far as I can tell to peruse too.
    https://360digitmg.com/course/project-management-professional-pmp

    ReplyDelete
  5. "
    Viably, the article is actually the best point on this library related issue. I fit in with your choices and will enthusiastically foresee your next updates.
    "
    https://360digitmg.com/hrdf-training

    ReplyDelete