Another challenge in String Manipulation

Question

Hi there

I've to manage the data into this and it has blank space in between:

0297144600-4799 0297485500-5599
0297144600-0297144799 0297485500-5599

0297144600-0297144799 0297485500-0297485599

I want to normalise the first/2nd row like the last row which means

0297144600-4799 >> 0297144600-0297144799

The challenge is that it can be on this format: 0297144600-4799 or already normalised and these entries can be more than 2 series for instance:

0297144600-4799 0297485500-5599 0297486500-6599

But always seperating by blank space (theoritically it can be replaced with '|' if you want). I do have a split method to do is but now how to combining this.

Try to avoid the cursor if it's possible.

Thanks

Answer 1

Is this something like what you had in mind?

declare @table table (old_pair nvarchar(21), new_pair nvarchar(21), pair_left nvarchar(10), pair_right nvarchar(10))

declare @abnormal nvarchar(max)
set @abnormal = isnull('0297144600-0297144799 0297485500-0297485599','')
--set @abnormal = isnull('0297144600-4799 0297485500-5599','')
--set @abnormal = isnull('0297485500-5599','')

declare @pair nvarchar(max)
declare @pair_left nvarchar(10)
declare @pair_right nvarchar(10)
declare @pair_right_length int

WHILE len(isnull(@abnormal,'')) > 0
BEGIN
    IF charindex(' ', @abnormal) = 0
        BEGIN
            set @pair = @abnormal
        END
    ELSE
        BEGIN
            set @pair = left(@abnormal, charindex(' ', @abnormal)-1)
        END
    set @pair_left = left(@pair, 10)
    set @pair_right_length = len(right(@pair,len(@pair)-11))
    set @pair_right = left(@pair_left, 10-@pair_right_length) + right(@pair,len(@pair)-11)

    insert @table
    (   old_pair,
        new_pair,
        pair_left,
        pair_right  )
    select 
        @pair, 
        @pair_left + '-' + @pair_right,
        @pair_left, 
        @pair_right

    IF @pair = @abnormal
        BEGIN
            set @abnormal = (right(@abnormal, len(@abnormal)-len(@pair)))
        END
    ELSE
        BEGIN
            set @abnormal = (right(@abnormal, len(@abnormal)-(len(@pair)+1)))
        END
END
    select * from @table