A while ago I had a problem where I wanted to change the first two characters in a table and I used the very simple concat and subst operations to do it with SQL. Today, we had a slightly more complex requirement of updating text in the middle of a field using SQL, or in other other words, squeezing some data into a field in a database.
Example: Imagine a flat file (aka table) has one big field (aka column) and we want to change two characters at position 69 & 70 dependent on certain conditions.
So, we are effectively squishing three things together — the beginning bit of data, our new characters and the end bit.
The trouble is, concat only allows two things to be squished together and since we need to update characters in the middle of a string -we are going to simply use concat twice:
update fileset set fieldA = concat( substr(fieldA,1,68) , concat('T1' , substr(fieldA,71))) where fieldB = 'stuff' and fieldC = 'otherstuff'
Works like a charm!
Special Offer for all NICKLITTEN Punters
20% Off with Coupon: NICKLITTEN
In Partnership with SNUG CBD - American readers get 20% off
CBD helps with relaxation, focus and great for pain relief. I highly recommend the SNUG CBD Tincture to help keep you in the zone when programming!