Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Category Archives: Text Manipulation

UK Postcodes can be difficult to handle in SQL, especially when trying to group results based on area code. Postcodes in the UK come in a variety of formats, the most common being AA9 9AA, A9 9AA, A99 9AA and AA99 9AA. Problems arise due to the variation in the total number of characters in a code, the different combinations of numbers and letters, and also the location of the space.

If you need to write a query to select the characters up to but excluding the space (e.g. AA9, A9, A99 or AA99), then the following approach can be used. It uses the LEFT command in conjunction with CHARINDEX to select the left side of the full code.

SELECT CompanyName, LEFT(Postcode, CHARINDEX(' ', Postcode) -1) AS 'Postcode'
FROM CompanyAddresses

However, if you need to select only the leading letters and not numbers (A or AA), then you can use a CASE statement:-

SELECT CompanyName, CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode'
FROM CompanyAddresses

Using either of the above two methods, you can successfully group results into Postcode areas. Use either method to group and select on Postcode.

SELECT CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode', COUNT(CompanyName) AS 'Companies in Postal Area'
FROM CompanyAddresses
GROUP BY CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END
Advertisements

Tags: , , , , , , , ,

Being able to replace characters in a string of text can be useful in solving a variety of problems. Thankfully SQL Server provides a REPLACE command which allows for sections of a string to be replaced with another set of characters.

Here is a simple example to demonstrate how REPLACE works:-

 SELECT REPLACE('My name is Fred', 'Fred', 'Frank')

Replace looks at the text string and replaces any instances of ‘Fred’ with ‘Frank’. Therefore, the output of the query will read:-

My name is Frank

When used in conjunction with an UPDATE command, we can use REPLACE to alter characters within a column. In this example we are changing the monthly salary of all staff from British pounds to US dollars. We are not converting the salaries, but simply changing the ‘£’ to a ‘$’, so in fact everyone is receiving a pay cut!

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_salary, '£', '$')

Instead of applying the change to all members of staff, what we should do is use a WHERE clause to ensure the REPLACE only affects those staff who should be getting paid in dollars:-

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_Salary, '£', '$')
WHERE nationality = 'American'

If we check the salary_table now, then we will find that records such as,

Bob Jones            American             £2,500

will now read as follows,

Bob Jones            American             $2,500

Tags: , , , , ,

%d bloggers like this: