Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Manipulating strings

Re: Manipulating strings

From: QuestionExchange <USENET_at_questionexchange.com>
Date: 18 Nov 1999 21:14:07 GMT
Message-ID: <2709qx@questionexchange.com>


> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US