How to Add Quotes to Matching Text in String

[former_SQLA_member1694868]

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?

[former_SQLA_member1694868]

Lots of good information here. Thanks to all.

[Former Member · Accepted solution]

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

UNLOAD SELECT * FROM table_name WHERE ... INTO VARIABLE @var

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:

begin
  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');
end

[VolkerBarth · Accepted solution]

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

[Breck_Carter]

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' );

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

[VolkerBarth]

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...

begin
  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';
  begin
    for forCrs as crs cursor for
       call sa_split_list(@line, ',')
    do
      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;
      endif;
      if @formatted_line is null then
        set @formatted_line = @token;
      else
        set @formatted_line = string(@formatted_line, ',', @token);
      end if;
    end for;
  end;
  select @line, @formatted_line;
end;

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...

[Former Member]

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

[VolkerBarth]

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

[MarkCulp]

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.

[Breck_Carter]

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' );?

[VolkerBarth]

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

[Breck_Carter]

@Volker: Padawan? ...so you saw the blog 🙂

[VolkerBarth]

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

[Former Member]

it was a nice photo too...