[SOLVED] How do I remove a character from strings of different lengths with sql? Intersystems cache sql

How do I remove a character from strings of different lengths with sql? Intersystems cache sql

I have a column of strings that have an '&' at the beginning and end of each one that I need to remove for a Crystal report I'm creating. I'm writing the SQL code outside of Crystal I am using Intersystems Cache SQL. Below is an example:

&This&         This
&is&           is
&What&         what
&it&           I
&looks&        need
&like&         it
&now&          to
               look
               like

Any suggestions would be greatly appreciated!!!

Solution

Assuming the ampersands are always positioned as both the leading and trailing characters, here's at least maybe a start. Use a combination of SUBSTR (or SUBSTRING, if using stream data) and LENGTH, like so:

SELECT SUBSTR((SELECT column FROM table), 2, LENGTH(SELECT column FROM table) - 2)

This should return a substring that starts counting at the 2nd character [of the original string, given by the first sub-expression/argument to SUBSTR], counting up for the total number of characters [of the original string] less 2 (i.e. less the two ampersands).

If you need to including trailing blanks and/or the string termination character, you may need to use a different variation of the LENGTH function. See resources for details on these functions and their variants:

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_substr

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_length