The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I have found many examples of how to remove things from strings.

In this case I want to search a string for timestamps and add single quotes around each.

So how to search this string:

2,,2010-11-04 10:13:45.805

and produce this result:

2,,'2010-11-04 10:13:45.805'

without knowing the exact text of the string, only knowing the format will match the timestamp format above.

It is preferable to code this within a block of existing code rather than calling a separate function.

What is the best way to handle this task?

asked 04 Nov '10, 16:53

Siger%20Matt's gravatar image

Siger Matt
accept rate: 13%


Lots of good information here. Thanks to all.

(05 Nov '10, 15:08) Siger Matt

Assuming (based on previous questions) that you are generating the string using:


you could modify the above statement to generate SELECT list items that convert all columns to strings, so that UNLOAD will automatically add quotes. To do this, you could take advantage of the sa_describe_query stored procedure to obtain a list of the columns in the query (or query the catalog tables SYSTAB and SYSTABCOL directly). Adding a CAST will force the conversion to string. In addition, you will need to handle null values. I would suggest outputting a special string for nulls, and using a replace() function call to convert these to NULL. Something like:

  declare @ul long varchar;
  declare @sellist long varchar;
  set @sellist =
    (select list('isnull(cast("' || name || '" as long varchar),''<null>'')' 
                order by column_number)
    from sa_describe_query('select * from ATB'));
  execute immediate
    'unload select ' || @sellist || ' from ATB '
            'where pk=2 into variable @ul ';
  select replace(@ul,'''<null>''','null');
permanent link

answered 05 Nov '10, 14:01

Bruce%20Hay's gravatar image

Bruce Hay
accept rate: 48%


@Bruce: I agree it's a better approach to export data in the expected format than to "correct" it lateron...

(05 Nov '10, 14:20) Volker Barth

In this particular case you could do the following:

  • search for the second comma and add a single quote after it

  • add a quote to the end of the line

Something like:

set @line = left( @line, locate( @line, ',', 2 ) )
         || ''''
         || substr( @line, locate( @line, ',', 2 )+1 )
         || '''';

This will work if:

  • there are a total of three columns

  • neither the first or second column values are quoted and contain embedded comma(s)

The general case is much harder if you need to content with quoted strings that contain embedded commas.

permanent link

answered 05 Nov '10, 12:33

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

So you're assuming this is a comma-delimited string? The specs say "search a string for timestamps" :) If it is comma-delimited then how about SELECT row_value, ISDATE ( row_value ) FROM sa_split_list ( '2,,2010-11-04 10:13:45.805' );?

(05 Nov '10, 12:56) Breck Carter

@Breck: That's what I mucked around with (see my answer) - obviously I'm still on the "Padawan" level:)

(05 Nov '10, 13:18) Volker Barth

@Volker: Padawan? you saw the blog :)

(05 Nov '10, 13:37) Breck Carter

@Breck: Yes, thanks for the very nice words and references! Now I wonder how can I upvote this blog post...

(05 Nov '10, 13:47) Volker Barth

it was a nice photo too...

(05 Nov '10, 17:01) Bill Aumen

EDIT: Code corrected...

The following code (now working - thanks to Elmi- see below) might be used to work with a list of unknown size and unknown position of any date/time/timestamp values.

It makes use of the handy sa_split_list and isdate functions. Note: Before isdate() can test the value, it must be enclosed with quotes...

  declare @line long varchar;
  declare @formatted_line long varchar; -- will contain the result
  declare @token long varchar;
  declare @test_token long varchar;
  set @line = '2,,2010-11-04 10:13:45.805';
    for forCrs as crs cursor for
       call sa_split_list(@line, ',')
      set @token = row_value;
      set @test_token = string('''', @token, '''');
      -- Original wrong statement
      -- if isdate(@test_token) = 1 then
      -- corrected statement
      if isdate(@token) = 1 then
        set @token = @test_token;
      if @formatted_line is null then
        set @formatted_line = @token;
        set @formatted_line = string(@formatted_line, ',', @token);
      end if;
    end for;
  select @line, @formatted_line;

Sadly enough, it doesn't seem to work as expected: Whereas isdate('2010-11-04 10:13:45.805') returns 1, isdate() for the @test_token with the exact value does not.

So you might treat this as a starting point at least...

permanent link

answered 05 Nov '10, 13:16

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 05 Nov '10, 13:44


The code works if you call isdate on @token, not @test_token.

(05 Nov '10, 13:25) Elmi Eflov

@Elmi: Thank you very much for the pointer - that's real "remote debugging":)

(05 Nov '10, 13:45) Volker Barth
SELECT row_value AS Token, 
       IF ISDATE ( row_value ) = 1 
          THEN 'Yes, a timestamp'
          ELSE 'No, something else' 
       ENDIF AS YesNo
  FROM sa_split_list ( '2,,2010-11-04 10:13:45.805' );

2,'No, something else'
,'No, something else'
2010-11-04 10:13:45.805,'Yes, a timestamp'
permanent link

answered 05 Nov '10, 13:35

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 04 Nov '10, 16:53

question was seen: 1,429 times

last updated: 05 Nov '10, 14:01