More regular expression help

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

More regular expression help

Post by barry.marcus »

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.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

More regular expression help

Post by mark »

sandr('>>=\RUS=.+','\3',TheField)
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

More regular expression help

Post by Kai »

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').
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

More regular expression help

Post by Kai »

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...
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

More regular expression help

Post by barry.marcus »

Thanks. Just what I needed.
Post Reply