Prepend existing data in SQL

Disclaimer: This tip will probably be really basic for most SQL folks.

The task: The company I work for has a web application that does some basic tracking of grants. The system feeding information to this application just had an across the board change to the numbering scheme of the grants. All grants must have a prefix of “999-“. Therefore, any grant not beginning with “999-” must be updated. Example: a grant with the current number 8789966 needs to be 999-8789966.

The solution: The SQL below does two things. It updates all the grants to prepend the 999- prefix while also skipping any grants that are already correctly prefixed.

UPDATE tbl_grants
SET grant_no = '999-' + grant_no
WHERE grant_no NOT LIKE '999-%'

2 thoughts on “Prepend existing data in SQL”

  1. Actually, it depends on what you are doing – you may need to use concat(‘999_’ + grant_no) in your example. That’s the only one that worked for me.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.