Character search question?????

[bgreen]

Hi,

I want to look for the last occurence of a space after the 70th character. How can I do this????

When I have a dollar value of 99,999,999.99 how do i get it to print out the words on two lines???

For instance:

** Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred --
Ninety-Nine Dollars and Ninety-Nine Cents*********************************

 

[nigelrivett]

datalength(@s) - charindex(' ',reverse(right(@s,datlength(@s)-70))


lol
You will need a translation table for this.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[PHV]

You want to do this in SQL ?
I hope for you that your RDMS has a consistent stored procedure language...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bgreen]

I figured it out with SQL.

I just had to do two separate select statements.

rpad(substr(AMOUNT,1,instr(AMOUNT,' ' ,70,1)), 76, '*') FIRST_SP,

rpad(substr(AMOUNT,instr(AMOUNT, ' ',70,1), length(AMOUNT) - instr(AMOUNT,' ',70,1) + 1),72, '*') SECOND_SP