Reformat the acct number in an sql result 1

[srosa]

I'm using a query feature in our company's application to communicate with Informix 3.8. I need to change the format of a resulting account number list in order to import this result to another program and link data based on that number.

This is an example of my statement:

Select
detail.strap
From
detail
Where
detail.map_id = 'Q22'
Order by
detail.strap

The result returns a list of acct numbers formatted as:
R-abc-defg-hijk.l

I need the resulting list to display the 'raw' acct # as:
abcdefghijklR

I have used the following formula in Crystal Reports to achieve this result, but don't know how to place it in this query to return the desired result or if there is another way to express this.

Mid ({detail.strap},3 ,3)+ Mid ({detail.strap}, 7, 4)+ Mid ({detail.strap}, 12, 4)+Right ({detail.strap}, 1)+ Left ({detail.strap},1)

Thanks for any help you can give me.

 

[PHV]

SELECT strap[3,5] || strap[7,10] || strap[12,15] || strap[17,17] || strap[1,1]
FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[srosa]

Thanks, but I still have a problem. I realized that the system is not seeing the 'R', 'dashes' or 'period'. The count begins at 'a'='1','b'='2',etc. and ends with 'l'='12'. I adjusted the formula and it works except that it leaves off the 'R'. The 'R' is definitely part of the raw strap so I'm not sure why it doesn't count it. Any suggestions about how to add it.
Thanks again for your help!

 

[PHV]

What is your actual code ?
could you please post some input samples with actual results vs expected output ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[srosa]

Between your initial suggestion and trying some things on my own, I solved my last problem pretty simply. I just added [||'R'] to the end of the formula and got the result I needed. Thanks for you help.