Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Unanswered: How do I remove delimiters in a string

    I have a string like
    [ID]123~^493~^[NAME_ID]~^1~^[FIRST_NAME]~^new~^[LAST_NAME]~^new~^

    I need to format it as
    FIELD Old Val New Val
    ID 123 493
    NAME ID 0 1
    FIRSTNAME new
    LASTNAME new

    Any help is appreciated . Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What does this have to do with Oracle RDBMS?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Since you didn't specify a language, I'll do it in Haskell:

    Code:
    -- Takes a string and breaks it on closing
    -- square brackets or ~^ sequences
    splitTilde :: String -> [String]
    splitTilde = foldr split [""]
       where
    	split :: Char -> [String] -> [String]
    	split ']' a = ("]":a)
    	split '~' (('^':w):a) = ("":w:a)
    	split c (w:a) = (c:w):a
    
    -- Breaks three item groups into tuples
    div3 :: [a] -> [(a, a, a)]
    div3 [] = []
    div3 (a:b:c:l) = (a,b,c):(div3 l)
    
    stripSquares x = [(tail $ init a, b, c) | (a, b, c) <- x]
    
    tweakLine :: String -> [(String, String, String)]
    tweakLine x = ("FIELD", "Old Val", "New Val") : (stripSquares $ div3 $ init $ splitTilde x)
    
    formatlot :: [(String, String, String)] -> String
    formatlot lot = unlines [(pad a cwa) ++ (pad b cwb) ++ (pad c cwc) | (a, b, c) <- lot]
        where
    	(cola, colb, colc) = unzip3 lot
    	cwa = cw cola
    	cwb = cw colb
    	cwc = cw colc
    	cw :: [String] -> Int
            cw = succ . maximum . map length
    	pad s w = take w (s ++ (repeat ' '))
    
    dolines inp = unlines $ map (formatlot . tweakLine) (lines inp)
    
    main = do
    	input <- getContents
    	putStrLn $ dolines input
    Save it as formatter.hs and build it with:
    Code:
    ghc --make formatter
    Then you have a program that will reformat your data for you.

    You can get the Haskell compiler for Mac, Windows or Linux here.
    Last edited by sco08y; 06-10-10 at 22:45. Reason: Build instructions.

  4. #4
    Join Date
    May 2006
    Posts
    132
    Code:
    SQL> select * from strings;
    
    A1
    -----------------------------------------------------------------------------
    [ID]123~^493~^[NAME_ID]~^1~^[FIRST_NAME]~^new~^[LAST_NAME]~^new~^
    
    SQL> with t as (
      2     select regexp_substr(a1, '[^\^]+\^[^\^]+\^',1,level) str from strings
      3     connect by level <= 4)
      4  select field,
      5         case when field = 'NAME_ID' then
      6             nvl(old_val, 0)
      7          else
      8             old_val end old_val,
      9         new_val
     10  from (
     11     select  ltrim(regexp_substr(str, '[^]]+'), '[') field,
     12             ltrim(regexp_substr(str, '][^~]*'), ']') old_val,
     13             ltrim(regexp_substr(str, '\^[^~]*'), '^') new_val from t);
    
    FIELD      OLD_VAL    NEW_VAL
    ---------- ---------- ----------
    ID         123        493
    NAME_ID    0          1
    FIRST_NAME            new
    LAST_NAME             new

Tags for this Thread

Posting Permissions

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