Thunderstone Support Forums

We have a table that contains document ids, some of which are of the form 'USxxxxxxxxxxxx'. I would like to use the sandr function in a sql select statement to return ONLY the part after the 'US'. (That is, return 'xxxxxxxxxxxx' instead of 'USxxxxxxxxxxxx'.) Seems simple enough, but I'm having difficulty. Thanks for your help.

sandr('>>=\RUS=.+','\3',TheField)

This assumes there is no other `US' in the source text, and that if anything is after the `xxx' part, it is whitespace (if either of these are not true, we'll need more details as to the ID syntax to fix the expression):

tsql ... "select sandr('.*>>US=[^\space]*.*', '\3', YourTextField) from YourTable"

I.e. look for `US', followed by non-space, with anything before and/or after those two; then replace with just the third sub-expression (the non-space after `US').

Use Mark's; it is more compact, if the field contains only `USxxx' and nothing else. For some reason I was assuming that might not be the case...

Thanks. Just what I needed.