Sifting Through Data

Today I found myself looking at a large amount of information in a database. I wanted to limit the data returned and so I enlisted the help of the substring function. There were thousands of rows of data that contained the word "mapping." I didn't want to see those rows in my query. So I asked the database for all the rows that didn't contain the substring "mapping". That gave me the information I was looking for and I was able to move onto my next project.

I was busily working when a coworker came up and asked me to change the data in another database. She started describing the problem and it sounded like it was going to be a huge task until I realized that it could be simplified with the use of the substring function. Basically I needed to remove the first 4 characters of one database field and replace it with a 5-character word. As I had been using the substring function earlier, the syntax or way of using it, was fresh in my mind. The SQL command looked similar to this:

UPDATE the_table
SET the_field = 'abcde' || substr(the_field,5)
WHERE another_field = 'xyz';

Going through the statement, the first line tells the database what table to update. The last line says we want the update to occur where a specific field is equal to the string "xyz". The second line shows the use of the substring or "substr" function. I didn't care about the first 4 characters of the existing string and so I started at position 5 (SQL strings start at character 1 while C programming language strings start at character 0, which can be confusing). I then concatenate it with the replacement string of "abcde". The two vertical pipe symbols represent the concatenate function in SQL.

The statement worked fine and I was free to get back to my other task. Amazingly I have not had to worry about the substring function for quite a while and I found it interesting that I needed it twice today.