Jim R Posted April 30, 2020 Share Posted April 30, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/ Share on other sites More sharing options...
requinix Posted April 30, 2020 Share Posted April 30, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577353 Share on other sites More sharing options...
Jim R Posted April 30, 2020 Author Share Posted April 30, 2020 It would just be the space in between the two words. Is the syntax close on what I typed in my OP? Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577354 Share on other sites More sharing options...
requinix Posted April 30, 2020 Share Posted April 30, 2020 ...yes? Like I said, the problem was that the regex needs to be a string. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577355 Share on other sites More sharing options...
Jim R Posted April 30, 2020 Author Share Posted April 30, 2020 Hard to believe there isn't a way to get what is essentially an acronym. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577356 Share on other sites More sharing options...
benanamen Posted April 30, 2020 Share Posted April 30, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577357 Share on other sites More sharing options...
Jim R Posted April 30, 2020 Author Share Posted April 30, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577358 Share on other sites More sharing options...
requinix Posted April 30, 2020 Share Posted April 30, 2020 MySQL is a database, not a programming language. Suck it up. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577359 Share on other sites More sharing options...
Barand Posted April 30, 2020 Share Posted April 30, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577362 Share on other sites More sharing options...
Jim R Posted May 1, 2020 Author Share Posted May 1, 2020 (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 May 1, 2020 by Jim R Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577369 Share on other sites More sharing options...
Barand Posted May 1, 2020 Share Posted May 1, 2020 4 hours ago, Jim R said: Not anymore You appear to be duplicating the city in most tables Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577379 Share on other sites More sharing options...
Jim R Posted May 8, 2020 Author Share Posted May 8, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310665-extract-first-left-of-each-word-in-a-column/#findComment-1577727 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.