0

I have one table with some values stored in keyword column, but values inside keyword column contain extra space while retrieving the data query is not fetching all records due to this extra space I tried using trim() in mysql but it didn't work for me

Expected output: mysql query should get all records by avoiding extra space following where condition.

Table:

id  keyword    
1   some text for product  //here  single  space after 'some'
2   some   text for product  // here two space after 'some'

Query:

SELECT * FROM `table` where trim(keyword)='some text for product'

After performing above query will return only single record not all records because of extra spaces in column values.

GMB
  • 216,147
  • 25
  • 84
  • 135
MSP
  • 57
  • 2
  • 8
  • trim would only remove leading and trailing characters defailt is space. and i don_'t know what do you expect from trim(keyword) = "some text for product' a keyword nother can be equal to a a text with some words. – nbk Oct 13 '19 at 11:15
  • actually i want to get all values from keyword column but it contain extra space so where query is not working – MSP Oct 13 '19 at 11:19
  • update your question and add a proper data sample .. and the expected result – ScaisEdge Oct 13 '19 at 11:22
  • search for sql replace spaces eg https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql , even though for sql server easily adapted for mysql.. – P.Salmon Oct 13 '19 at 11:43
  • Is there a (good) reason that your data contains a double space? Otherwise make sure your data is stored in a proper way (e.g. remove the double spaces before you store them). Storing invalid/useless/suboptimal data is a cause for a lot of headache. You may also want to look into fulltext search, it will allow you some leniency when searching random text in random text (e.g. spaces, points, word orders, ...) and may (or may not) work too. – Solarflare Oct 13 '19 at 11:47

1 Answers1

0

TRIM() removes spaces at the beginning or the end of the string. You basically want to replace double spaces within the string with single spaces. You can use REPLACE() instead:

SELECT * FROM `table` WHERE REPLACE(keyword, '  ', ' ') = 'some text for product'

Demo on DB Fiddle:

WITH `table` AS (
      SELECT 'some text for product' keyword
      UNION ALL SELECT 'some  text for product' keyword
)
SELECT * FROM `table` WHERE REPLACE(keyword, '  ', ' ') = 'some text for product';

| keyword                |
| ---------------------- |
| some text for product  |
| some  text for product |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • @MSP: I added a db fiddle to my answer; this works fine for the sample data that you provided. – GMB Oct 13 '19 at 11:59