Angie Angie - 5 months ago 8
SQL Question

SQL Removing every abbreviated string until a space

I've got two different values in the same column.
Example: ColumnName = COUNTY
Example Data: "NSW New South Wales"
How can I setup an update/replace query to remove NSW abbreviation and the space and have "New South Wales" only?
I have different abbreviations per County and I'd hate to do a replace function one by one.

Answer

Try using SUBSTRING_INDEX with REPLACE()

UPDATE YourTable t
SET t.YourColumn = LTRIM(REPLACE(t.YourColumn,SUBSTRING_INDEX(t.YourColumn,' ',1),''))

For SQL-Server use SUBSTRING and CHARINDEX:

UPDATE YourTable t
SET t.yourColumn = SUBSTRING(t.YourColumn,CHARINDEX(t.YourColumn,' ') + 1,100)