Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Split and replace function in sql

    Hi Guys,

    Actually i want to perform some string operation and the resultset returned by a query, but im not sure how to do it

    i have the following query

    Code:
    select types.name as type,parameter.name as name, parameterdefault.defaultvalue as defaultvalue,
    case
       WHEN types.name = 'layer' then ''
       WHEN types.name = 'p_mil' then ''
       ELSE NULL
    END results
    from parameter,parameterdefault,types
    where parameter.parameterid = parameterdefault.parameterid
    and parameter.typeid = types.typeid

    Actually what i wish to do is some case statement,

    if types.name = 'layer' then replace ";" with ',' and add
    NCE(" ") between each item in the default values,

    I can do a case statement, but im not sure how to do a replace
    statement from ';' to ',' and add NCE(" ") in each item

    Appreciate if someone could guide me here on how to replace
    and append some string to the beginning and endding of each and every item item

    So if the defaultvalue in the query returned something like 0,1000
    when i query, the results returned for defaultvalue column should be NCE("0"),NCE("1000")

    Thank you very much for any guidelines

  2. #2
    Join Date
    May 2005
    Posts
    14
    Assuming all your default values are ; separated pairs, the following seems to work

    select 'NCE("' || substr('0;1000', 1, instr('0;1000', ';')-1)
    || '"),NCE("'
    || substr('0;1000', instr('0;1000', ';')+1) || '")'
    from dual

    Just replace all the 0;1000 string values with your column value

    Other string manipulation commands that may help you are "replace" and "translate"
    Hope this helps

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    hi robdearden,

    The query works excellent, thansk alot.

    But from what i have observed, it only works on two separed ';'

    Meaning if the data in the defaultvalue column is 0;1000
    then it generates NCE("0"),NCE("1000"), which is great.

    But if the data in the defaultvalue column is 0;1;2
    it generates NCE("0"),NCE("1;2")..

    how can i change the query to be dynamic, meaning each item
    gets appended by the NCE("") string

    So lets say if i have 4 items, 0;1;2;3
    I would get NCE("0"),NCE("1"),NCE("2"),NCE("3")

    Because sometimes i might have 2 items, sometimes 3 or maybe 4 or more

    So how can i make it more flexible.

    thank you very much for any advice.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about:

    Code:
    select 'NCE("' || replace(defaultvalue, ';', '"),NCE("') || '")' 
    from mytable;
    I'm sure to have made a mistake there somewhere, but you get the idea?

  5. #5
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by andrewst
    How about:

    Code:
    select 'NCE("' || replace(defaultvalue, ';', '"),NCE("') || '")' 
    from mytable;
    I'm sure to have made a mistake there somewhere, but you get the idea?
    Hi andrewst, yea i got the idea, the code works excellent, thanks a million,
    just exactly the same as what i wanted
    appreciate it, i didnt knew it can be done in a single replace statement, thought it required some kind of looping between each item..
    thank you very much
    Last edited by a1jit; 04-09-06 at 23:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •