Other approach for handling this TSQL text manipulation

Question

Hi there

I have this following data:

0297144600-4799               0297485500-5599

The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach.

But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid:

0297144600-4799      0297485500-5599      0297485600-5699

As you can see, I guess the first approach will the split by 1 blank space (" ") but due to number of space is unknown (varies) how do I take this approach then? Is there any method to find the space in between and shrink into 1 blank space (" ").

BTW ... it needs to be done in TSQL (Ms SQL 2005) unfortunately cause it's for SSIS :(

I am open with your idea/suggestion.

Thanks

Answer 1

Assuming you're processing one line at a time, you can also try this:

DECLARE @InputString nvarchar(max)
SET @InputString = '0297144600-4799      0297485500-5599      0297485600-5699'
BEGIN
WHILE CHARINDEX('  ',@InputString) > 0  -- Checking for double spaces
  SET @InputString =
    REPLACE(@InputString,'  ',' ') -- Replace 2 spaces with 1 space
END
PRINT @InputString

(taken directly from SQLUSA, fnRemoveMultipleSpaces1)

Answer 2

I have updated my answer a bit, now that I know the number pattern will not always match. This code assumes the sequences will begin and end with a number and be separated by any number of spaces.

DECLARE @input nvarchar -- max in parens
DECLARE @pattern nvarchar -- max in parens
DECLARE @answer nvarchar -- max in parens
DECLARE @pos int
SET @input = '      0297144623423400-4799      5615618131201561561     0297485600-5699         '

-- Make sure our search string has whitespace at the end for our pattern to match
SET @input = @input + ' '

-- Find anything that starts and ends with a number
WHILE PATINDEX('%[0-9]%[0-9] %', @input) > 0
BEGIN
  -- Trim off the leading whitespace
  SET @input = LTRIM(@input)
  -- Find the end of the sequence by finding a space
  SET @pos = PATINDEX('% %', @input)
  -- Get the result out now that we know where it is
  SET @answer = SUBSTRING(@input, 0, @pos)
  SELECT [Result] = @answer
  -- Remove the result off the front of the string so we can continue parsing
  SET @input = SUBSTRING(@input, LEN(@answer) + 1, 8096)
END