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

[Jim R]

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.  

[requinix]

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.

[Jim R]

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

[requinix]

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

[Jim R]

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

[benanamen]

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.

[Jim R]

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.

[requinix]

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

[Barand]

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.

[Jim R]

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 May 1, 2020 by Jim R

[Barand]

4 hours ago, Jim R said:

Not anymore

You appear to be duplicating the city in most tables

[Jim R]

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.