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:
and produce this result:
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
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:
answered 05 Nov '10, 14:01
In this particular case you could do the following:
This will work if:
The general case is much harder if you need to content with quoted strings that contain embedded commas.
answered 05 Nov '10, 12:33
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.
So you might treat this as a starting point at least...
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'
answered 05 Nov '10, 13:35