SAP SQL Anywhere

Question

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?

Accepted Answer

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

Answer 2

1

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 Culp
24.9k●10●141●297
accept rate: 41%

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

1

@Volker: Padawan? ...so 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

flat view

Answer 3

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

Answer 4

1

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'

permanent link

answered 05 Nov '10, 13:35

Breck Carter
32.5k●541●726●1050
accept rate: 20%

flat view

SAP SQL Anywhere

How to Add Quotes to Matching Text in String

Follow this question

Related questions