Clever Geek Handbook

How do I remove spaces in an abbreviation in SQL?

I want to write a function that removes spaces between isolated letters, for example in an abbreviation.

For example, if I have:

'I B M Computers' i need to have in return 'IBM Computers'
' I B M ' => 'IBM'
'Computers A B' => 'Computers AB'
'Computers A BC' => 'Computers A BC' (nothing changes)
'Computers A B CD' => 'Computers AB CD'
'Computers A B C D' => 'Computers ABCD'

I cannot find a way to do this correctly, can anyone help me?

Thank.

+3


source to share


3 answers


A regex as described in this post should work for you (with a little regex tweak for MariaDB). From this post by @Alan Moore: "It seems to me that you want to remove any space that (1) must precede a letter that does not itself precede a letter, and (2) that is followed by a letter that is not followed by a letter. These conditions can be expressed exactly as nested images. " Please do it justice.

fooobar.com/questions/2413771 / ...

So, if your column is named DESC, your answer would be:



SELECT TRIM(REGEXP_REPLACE(DESC, '(?<=(?<![[:alpha:]])[[:alpha:]]) +(?=[[:alpha:]](?![[:alpha:]]))','')) AS fixed_desc
FROM table;

Test your regex here

+2


source


Try using the ltrim and rtrim functions. Example: Update 'table' Set campo = ltrim (rtrim (campo))



0


source


Use the replace function to strip whitespace from both sides of the comparison.

But note that this will be inefficient as your query will not be able to use the indexes

REPLACE(mystring,' ','')

https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

0


source






More articles:


All Articles