Fun with SQL: Common Table Expressions

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

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'

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

(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 (
  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%')
  FROM userAddresses
  WHERE adrPhoneExt = '%example%'
ORDER BY lastName