Solved

How to search a string in a mysql text field

Posted on 2014-03-07
4
904 Views
Last Modified: 2014-03-07
Hello,

I need to search a string in a mysql text field that could contains newlines chars. Using the standard LIKE operator it does not work because the string searched is different from the text field as it contains newlines.

Here is an example:
select id from fb_posts where message like '%Fineco App. Tutti i servizi, in tutti i device. Da dove preferite collegarvi? https://welcome.fineco.it/bancaonline/extra/fineco-app%'

the string could not be found because the text field is:

Fineco App. Tutti i servizi, in tutti i device.
Da dove preferite collegarvi?

https://welcome.fineco.it/bancaonline/extra/fineco-app

So I think there should be e method to search removing newline chars.

thank you
0
Comment
Question by:apedic
  • 3
4 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39912021
Try
select id from fb_posts where message like '%Fineco App. Tutti i servizi, in tutti i device.\n Da dove preferite collegarvi? https://welcome.fineco.it/bancaonline/extra/fineco-app%'

Open in new window

or
select id from fb_posts where message like '%Fineco App. Tutti i servizi, in tutti i device.\r\n Da dove preferite collegarvi? https://welcome.fineco.it/bancaonline/extra/fineco-app%'

Open in new window

if the text is from Windows.

HTH,
Dan
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39912023
Or
SELECT id FROM fb_posts WHERE REPLACE(message,"\n","") LIKE '%Fineco App. Tutti i servizi, in tutti i device. Da dove preferite collegarvi? https://welcome.fineco.it/bancaonline/extra/fineco-app%'

Open in new window

0
 

Author Comment

by:apedic
ID: 39912074
thank you, works perfectly!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39912093
Glad I could help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now