16:42

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

New sql features

Common table expressions (CTE)

Recursive CTE page 2

Let's solve another challenge as the answer to the question that I have held numerous meetings on the Internet landscape.

Convert text in a table column in such a way that each word begins with an uppercase letter.

Here's an example of data and the desired result:

name	rep
delta	Delta
KSI PSI	Ksi Psi
alfa beta gamma zeta	Alfa Beta Gamma Zeta
ALfa and omegA	Alfa And Omega

For a few exceptions (among which may be mentioned abbreviations and initials) can be assumed that the word inside the text is preceded by a space. This can be used as a search criterion we need the elements of the text. I propose to implement a following trivial algorithm:

1. The first letter of the text makes a capital, and the rest - lower.

2. Then each "space + letter" combination translates to upper case.

From the first paragraph of the algorithm's simple:

Console

Execute

SELECT name, UPPER(LEFT(name, 1)) + 
LOWER(SUBSTRING(name, 2, LEN(name) - 1)) rep
FROM
(SELECT 'ALfa and omegA' AS name
UNION ALL SELECT 'alfa beta gamma    zeta'
UNION ALL SELECT 'KSI PSI'
UNION ALL SELECT 'delta'
) X;

name	rep
ALfa and omegA	Alfa and omega
alfa beta gamma zeta	Alfa beta gamma zeta
KSI PSI	Ksi psi
delta	Delta

To implement the second paragraph, there are some ways. Since the Latin alphabet is not so much (26), we can just make 26 substitutions. I was not lazy and will bring the full query so you can experiment.

So,

Console

Execute

SELECT name, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(rep, ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'),
 ' e', ' E'), ' f', ' F'), ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), 
' l', ' L'), ' m', ' M'), ' n',' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R'), 
' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), 
' x', ' X'), ' y', ' Y'), ' z', ' Z')
FROM(
SELECT name, UPPER(LEFT(name,1)) + LOWER(SUBSTRING(name, 2, LEN(name)-1)) rep
FROM
(SELECT 'ALfa and omegA' AS name
UNION ALL SELECT 'alfa beta gamma    zeta'
UNION ALL SELECT 'KSI PSI'
UNION ALL SELECT 'delta'
) X
) Y;