Jump to content

Extract first left of each word in a column...

Jim R

Recommended Posts

I have a column = city   

In some instances, I just need the first the letter of each word in the city.  Some city names are more than one word.  

So Flora would be (F) and Fort Wayne (FW).


It's the WHEN toggle = 2   ... line.

$query="SELECT id,
			WHEN toggle = 1 THEN concat(city,' ',school)
			WHEN toggle = 2 THEN concat(school,' (',substring(city,1,1),')')
			ELSE school
	END) as schools
	FROM a_schools
	ORDER BY schools


Instead of substring i've tried acronym and initials.


 I found those in a search, but I got errors suggesting they weren't valid options.  


I've also tried regex_replace(city, [a-z], '') but got syntax errors too.  Could be I didn't apply it right.  

Link to comment
Share on other sites

The closest I can come up with after a few minutes of thought is (also) to use regex_replace, but to remove not just lowercase letters but anything that isn't an uppercase letter. That will include punctuation too. It won't handle all city names very well, though. What you really need is a column for the shortened form of the location.

The problem with your attempt at it is that the regular expression is a string.

Link to comment
Share on other sites

Between LEFT, RIGHT, SUBSTRING, SUBSR and a couple of more, there seems to be 4-6 ways I could just get the first letter of each field.  None to get the first letter of each word?

Code gymnastics?  Anymore than using CASE?  

Normally I'm hammered on here for having too many columns or too many times duplicating data.

Link to comment
Share on other sites

On 4/30/2020 at 12:28 AM, requinix said:

MySQL is a database, not a programming language. Suck it up.

It's not a problem, just noting the irony of the situation.  Still surprised there isn't a statement to get acronyms, or at least some way to use substring, noting a delimiter.  


19 hours ago, Barand said:

Yet you still do it, which is why I have given up.

Not anymore.  All IDs now.  

Edited by Jim R
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.