how to remove newline columns in mysql while getting the data from database

Question

For example in my form address field is there. It will stored in newlines in a database(If i click enter and add text in address field). Now i would like search with full address search.Results will not getting.here my query is like this

select * from contacts where address like '69 TEXT STREET,4th Floor boston 2TN'

I saw some posts in stackoverflow. what i used for replacing newlines in a column is

REPLACE(REPLACE(address, '\r', ''), '\n', '')

if i used like this in my query data is not retrieved

select * from contacts where REPLACE(REPLACE(address, '\r', ''), '\n', '') like '69 TEXT STREET,4th Floor boston 2TN'

I need exact search results.Thanks in advance

Alexander Gelbukh · Accepted Answer · 2014-07-23 06:53:09Z

1

You should replace newlines with spaces, not with empty string

REPLACE(REPLACE(address, '\r\n', ' '), '\n', ' ')

because if the user types

1 Microsoft Way
Redmont

and you search for

1 Microsoft Way Redmont

you use a space where she used newline. Also, you probably should remove all commas etc. from both the query and the database, to match also

1 Microsoft Way, Redmont

answered Jul 23, 2014 at 6:53

Alexander Gelbukh

2,2601 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Krishna38 Over a year ago

This one also i tried. but i am unable to get the result.could you tell me what is the problem.?

Alexander Gelbukh Over a year ago

Try printing SELECT REPLACE(REPLACE(address, '\r', ''), '\n', '') FROM contacts` and comparing with your query. You might find what the problem is. You may reduce the search space by WHERE address LIKE 'TEXT STREET' or so, then manually find your address, and then see what's the problem.

Krishna38 Over a year ago

yeah.. i tried like that.I got empty result set if address is stored in two lines in DB. if address stored like a single line i got search results. I am not trimming or removing line breaks while inserting the data into DB.

Alexander Gelbukh Over a year ago

I forgot to mention the % above: WHERE address LIKE '%TEXT STREET%' and use a part of your address that cannot contain a newline (say, only one word) and would return few results. The % means you only search for a substring, not for the exact string.

Krishna38 Over a year ago

yes i tried.now it will come. but i need exact text search result.for suppose if i use REPLACE(REPLACE(address, '\r', ''), '\n', '')** it removes the second line data in the database. For example 69 TEXT STREET, stored in single line and next 4th Floor boston 2TN stroed in second line. User will search total string. if i use that command it retrieves the data upto 69 TEXT STREET, . i need total search string result.

|

Yunus Aslam · Accepted Answer · 2014-07-23 06:53:31Z

0

Try using '% Search item %' this way ..

select * from contacts where REPLACE(REPLACE(address, '\r', ''), '\n', '') like '%69 TEXT STREET,4th Floor boston 2TN%'

answered Jul 23, 2014 at 6:53

Yunus Aslam

2,4764 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

how to remove newline columns in mysql while getting the data from database

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related