MySQL Select

Substring

[gosman]

I'm trying to slect a certain amount of text from a column but stop at the first period.

Example if I have a column called DESCRIPTION and I issue the following command.

select DESCRIPTION from DB

And the following is returned

"I want to select text up to this point. But not this"

What command would I issue to retreive the text up to the first period. So the following is returned.

"I want to select text up to this point."

Thanks in advance for any help.

[cmarshall]

I don't know if you can filter the data at the SQL level. I don't know, because I never do. Even if it were possible, it would almost certainly be a major performance hit.

Most of the SQL fanciness comes in constructing search queries (which can have some pretty intense performance ramifications themselves).

Just get the whole thing, and then use PHP to REGEX it with preg_match or preg_replace.

[gosman]

Hi cmarshall

I only need to do this once. I posted in this forum because there isn't a dedicated MySQL forum.

I need to do this at MySQL level if possible.

[cmarshall]

You may be SOL.

Look here [dev.mysql.com] to see if you can find something.

Good luck. Let us know if you do.

[coopster]

You can indeed do this at the database level using a combination of two functions, SUBSTRING and POSITION. There is a database forum here at WebmasterWorld and there you will find a thread titled Display only part of a MYSQL field [webmasterworld.com] with some examples.

[cmarshall]

Sounds like you got an answer, and I learned something as well.

[barns101]

And I learned what SOL stands for! ;)

[gosman]

Hi Guys.

Thanks for your help, but I'm a complete newbie to MySQL and PHP so the thread mentioned is like reading a foreign language.

Can anybody show me an example command based on the above scenario.

Thank you.

[jatar_k]

there are examples in the thread coopster posted

[gosman]

As I said I'm a complete newbie and the thread doesn't make sense. I have played around with a few of the examples but am unable to acheive the required result.

Can anybody provide an example?

[cmarshall]

As I said I'm a complete newbie

In that case, I'd HIGHLY recommend not trying to do it this way. It's a pretty advanced capability.

Fancy MySQL scripting is always a royal pain to get right.

[coopster]

It really isn't difficult gosman. You just need to have a look at the functions mentioned and apply them. Read about the functions, develop your query, and start testing. The only way to learn is to make some attempts, make mistakes, and learn from your mistakes. The key word here is 'make', or take action, apply yourself. Here is a working example you can run from a command line to see how this works:

SELECT SUBSTRING('I want to select text up to this point. But not this' FROM 1 FOR POSITION('.' IN 'I want to select text up to this point. But not this')) AS mySubstring; 
// Returns: 
I want to select text up to this point.

This says to grab a substring of the text starting FROM character number 1 in the text and continue FOR <whatever character POSITION the first period is found> in the text. We top it all off by giving the entirely new value an ALIAS of 'mySubstring'. The 'mySubstring' will be the index you can use in your PHP code to retrieve the value from the row, such as

$row['mySubstring']

.

Now to apply it to your table we simply replace the quoted text with your table's column name (

description

) and make sure we select from your table:

SELECT SUBSTRING(description FROM 1 FOR POSITION('.' IN description)) AS mySubstring FROM myTable;

This says to grab a substring of the

description

column starting FROM character number 1 in the value of the column and continue FOR <whatever character POSITION the first period is found> in the column FROM your table. We used the ALIAS once again as described earlier.

It's truly quite simple once you read and understand the functions. Give it a shot and tell us how you fare.

[cmarshall]

Wow. Thanks for the lesson, Coopster!

I certainly learned something from this. I'd still suggest that it may not be that simple for people just starting off in this. However, your points about learning are absolutely 100% correct. It's how I learned (and still do).

There's an old saying: "Good judgment comes from experience. Experience comes from bad judgment."

[coopster]

You're welcome, cmarshall and thanks for recognizing what I am trying to say here. Words in a forum often don't relay our intentions and emotions as well as we hope they do. My intention is not to scold or belittle or correct anybody but to simply state that the only way to overcome obstacles is application. I went through it and continue to go through it, but what comes from the experience is the knowledge that we can apply and pay forward.

Don't be afraid to jump off and try things, ask questions, make mistakes and learn from them. Trying first, struggling through, and resolving on your own is very rewarding. Getting stuck and asking for help is inevitable. We all reach these plateaus, if we aren't, we aren't pushing ourselves hard enough. But we need to reach this point by hitting the wall once in awhile. And finding friends in a community to help when we reach this point is priceless. Try. Learn. Return the favor someday.

[gosman]

Thanks coopster worked a treat. Much appreciated.