Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Manipulating strings
> Hello everyone,
>
> I'm working with visual basic 6.0 and I have a
> DBCombo that is automatically filled with two
> fields of one of my table. But I want my field
> Code, who look like '60010' to be shown like '600-
> 10'.
>
> Here's my code:
>
> dim ODyn as oraDynaset
>
> Set ODyn = objDb.CreateDynaset("SELECT Code, Code
> || ' ' || Name AS Expr1 FROM myTable ORDER BY
> Code", 0&)
> Set ORADC.Recordset = ODyn
>
> Result:
> 60010 XXXXXXXXXXXXXXXXXXXXXXX
> What I want:
> 600-10 XXXXXXXXXXXXXXXXXXXXXXX
>
> Can anybody help me?
Hi there,
It should be pretty simple, using the SUBSTR SQL function
which returns substrings of a string: SUBSTR(S,n,m) returns
the substring of S made by selecting m characters starting
from the nth character
Assuming the field Code is a string (otherwise, apply TO_CHAR
to it), and that it has a fixed length of 6 chars, the
following
code will do the job:
Set ODyn = objDb.CreateDynaset("SELECT SUBSTR(Code,1,3) ||
'-' || SUBSTR(Code,4,2) , Code || ' ' || Name AS Expr1
FROM myTable ORDER BY Code", 0&)
I hope this helps!
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=7549&cus_id=USENET&qtn_id=7984
Received on Thu Nov 18 1999 - 15:14:07 CST