Insert a character into the middle of a string
I want to insert a '=' in the middle of a string (EXCH_CODE).
The placement of the '=' character is dependent on the value in another column (DELIMITER).
So what I should end up with is;
I am guessing that I need to split the EXCH_CODE in two first and then concatenate the 2 strings with the '='.
So far I have
SELECT SUBSTR(EXCH_CODE, 1, SUBSTR(DELIMITER, 1, 1))
which gets the 1st half of the string.
I have just had a think and play and managed to come up with the following, and it works.
So if anyone has a similar issue, I hope this is of help;
SELECT EXCH_CODE, SUBSTR(EXCH_CODE, 1, SUBSTR(DELIMITER,1,1)) || '=' || SUBSTR(EXCH_CODE,SUBSTR(DELIMITER,1 ,1)+1,LENGTH(EXCH_CODE))
What it does is create a substring starting at char 1 for the length of the Delimiter value, and concatenat with a '=', and then concatenate all of that to the 2nd string, which begins at the Delimiter value +1 (so it starts at the next character - otherwise you would have a duplicate character in your string) for the length (number of characters) in the string.
Just don't ask me to reproduce it. Ha ha.
Unless anyone knows a better way to do this?
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL