Jump to content
Jim R

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

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,
	(CASE
			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.

acronym(city)  

 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.  

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

It would just be the space in between the two words.  Is the syntax close on what I typed in my OP?

Share this post


Link to post
Share on other sites

...yes? Like I said, the problem was that the regex needs to be a string.

Share this post


Link to post
Share on other sites

Hard to believe there isn't a way to get what is essentially an acronym.  

Share this post


Link to post
Share on other sites
24 minutes ago, requinix said:

What you really need is a column for the shortened form of the location.

I agree. Stop doing code gymnastics and just store the data the way you want it.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
12 hours ago, Jim R said:

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

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

Share this post


Link to post
Share on other sites
Posted (edited)
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

Share this post


Link to post
Share on other sites
4 hours ago, Jim R said:

Not anymore

You appear to be duplicating the city in most tables

Share this post


Link to post
Share on other sites
On 5/1/2020 at 12:33 PM, Barand said:

You appear to be duplicating the city in most tables

I've restructured all the tables.  

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
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.