/dev/null

I had heaps of fun with SQL today (again). Ever heard of Common Table Expressions? Here is an example of a recursive query:


WITH
nodeCTE (nodeId, nodeName)
AS (
SELECT
a.nodeId,
nodeName = CONVERT(varchar(8000), nodeName)
FROM
nodeNames a JOIN nodes b ON a.nodeId = b.nodeId
AND a.locale = 'en' AND b.parentId = 0
UNION ALL SELECT y.nodeId,
x.nodeName + ' -> ' + CONVERT(varchar(8000),
z.nodeName)
FROM
nodeCTE x JOIN nodes y ON y.parentId = x.nodeId
JOIN nodeNames z ON z.nodeId = y.nodeId AND
z.locale = 'en'
)
SELECT * FROM nodeCTE;


Another task was to create a database record in the locale ‘de’, if it did not exist yet:


INSERT INTO users
(userId, locale, title, firstName, lastName,
middleName, nobleName, displayName, email)
SELECT
userId, 'de', title, firstName, lastName,
middleName, nobleName, displayName, email
FROM
users b
WHERE
locale = 'en' AND NOT EXISTS
(
SELECT NULL
FROM mdb_users a
WHERE a.userId = b.userId AND locale = 'de'
);


Afterwards, it was easy to eliminate all special (German) characters from the original records in the default locale ‘en’.
Did you know that there is a major difference between German and English is the abbreviation for “Doctor”, which is “Dr” in English and “Dr.” in German? That had to be fixed too:

UPDATE users
SET title = REPLACE(title, 'Dr.', 'Dr')
WHERE locale = 'en';


Last query example is a search for name parts and the phone extension. You had to search in all locales of the multi-lingual database, but only return distinct results in the locale ‘en’ with Name und Initial:


SELECT
displayName, initial
FROM
users
WHERE
locale = 'en'
AND
userId IN


(
SELECT
userId
FROM
users
WHERE
(lastName LIKE '%example%' OR
firstName LIKE '%example%' OR
middleName LIKE '%example%' OR
nobleName LIKE '%example%' OR
title LIKE '%example%' OR
initial LIKE '%example%')
UNION SELECT
userId
FROM
userAddresses
WHERE
adrPhoneExt = '%example%'
)


ORDER BY
lastName