[RBASE-L] - Re: Brain Freeze on Stripping Spaces and Characters from a String

[Paul Buckley]

Jason,

 

Thanks for your suggestion, I'm working on something this morning but I'll
look at this later today and get to you..

 

Thanks again,

Paul Buckley

 

From: rbase-l@rbase.com [mailto:rbas...@rbase.com] On Behalf Of Jason Kramer
Sent: Wednesday, November 18, 2009 9:16 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Brain Freeze on Stripping Spaces and Characters
froma String

 

I had to do something similar a while back.  I have modified my solution and
pasted it below.  Please let me know what you think.

SET VAR voristring TEXT = 'Test with spaces and non-alphanum characters:
$...@.'

SET VAR vstrlen INTEGER = NULL
SET VAR vcurpos INTEGER = 1
SET VAR vcurchar TEXT = NULL
SET VAR vnospaces TEXT = NULL
SET VAR vnewstring TEXT = NULL

PAUSE 2 USING .voristring CAPTION 'Before'

SET VAR vnospaces = (SRPL(.voristring,' ', '',0))
SET VAR vstrlen = (SLEN(.vnospaces))

WHILE vcurpos <= .vstrlen THEN
  SET VAR vcurchar = (SGET(.vnospaces,1,.vcurpos))

  IF (ISALPHA(.vcurchar)) = 0 AND (ISDIGIT(.vcurchar)) = 0 THEN
    IF vcurpos = 1 THEN
      SET VAR vnewstring = '_'
    ELSE
      SET VAR vnewstring = .vnewstring + '_'
    ENDIF
  ELSE
    IF vcurpos = 1 THEN
      SET VAR vnewstring = .vcurchar
    ELSE
      SET VAR vnewstring = .vnewstring + .vcurchar
    ENDIF
  ENDIF

  SET VAR vcurpos = .vcurpos + 1
ENDWHILE

PAUSE 2 USING .vnewstring CAPTION 'After'

CLEAR VARIABLES voristring, vstrlen, vcurpos, vcurchar, vnospaces,
vnewstring

RETURN



Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)



Paul Buckley wrote: 

I know I'm missing something simple, probably not enough caffeine today.
I'm trying to setup a computed column in a table that will strip all spaces
from the string and replace all the non-alpha or numeric characters with an
underscore (_).  Here's some examples; First & Third would become
First_Third, First/Third would become First_Third and Item # would become
Item_.
 
Can someone suggest a way to do this please.  Thanks in advance.
 
Paul Buckley