Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Replace text help

    Hello,

    I need to do the following data manipulation for a given field I'm storing:

    The following string:
    Cash Back_Requires Redemption _$5 for 12 Months Cl $0.00
    Would be translated to:
    Cash Back $0.00

    Basically, when there is a price associated with an element and an underscore is present, the text that appears from the underscore to the price should be suppressed.

    I would love to run this in one batch instead of using the app layer to loop over all the records since there can be many (100s of thousands). Can someone shed some light regarding the syntax to run an update statement to clean this data?

    TIA

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    declare @string Varchar(512)

    set @string = 'Cash Back_Requires Redemption _$5 for 12 Months Cl $0.00 '


    Select SUBSTRING(@string, 1, CHARINDEX('_', @string,1) -1) + ' ' + Reverse(SUBSTRING(Reverse(@string),1,CHARINDEX('$' , Reverse(@string),1)))
    Cheers....

    baburajv

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    What if there's a negative value, as such:

    Cash Back_Requires Redemption _$5 for 12 Months Cl -$20.00

    Thanks!!

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    more circus..

    Declare @vt_StringTable Table
    (
    MyString Varchar(512)
    )

    Insert into @vt_StringTable
    (
    MyString
    )
    Select 'Cash Back_Requires Redemption _$5 for 12 Months Cl -$50.00 '
    Union
    Select 'Cash Back_Requires Redemption _$5 for 12 Months Cl $50.00 '
    Union
    Select 'Cash Back_Requires Redemption _$5 for 12 Months Cl -$250.00 '
    Union
    Select 'Cash Back_Requires Redemption _$5 for 12 Months Cl $750.00 '

    Select SUBSTRING(MyString, 1, CHARINDEX('_', MyString,1) -1) + ' '+
    Case When CharIndex('$',Reverse(MyString),1) <> 0
    Then Case When Substring(Reverse(MyString),CharIndex('$',Reverse( MyString),1) + 1,1) = '-'
    Then Reverse(Substring(Reverse(MyString),1, CharIndex('$',Reverse(MyString),1) + 1))
    Else Reverse(Substring(Reverse(MyString),1, CharIndex('$',Reverse(MyString),1) ))
    End
    End
    From @vt_StringTable



    btw, why do we have such a requirement? won't it be easy if the same info is stored in separarte columns than in a string??
    Cheers....

    baburajv

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Simplifying at bit . . .

    Code:
    Select	SUBSTRING(MyString, 1, CHARINDEX('_', MyString,1) -1) + ' '+
    	Case
    		When CharIndex('$ ',Reverse(MyString),1) <> 0 Then Reverse(Substring(Reverse(MyString),1, CharIndex('$',Reverse(MyString),1) ))
    		When CharIndex('$- ',Reverse(MyString),1) <> 0 Then Reverse(Substring(Reverse(MyString),1, CharIndex('$',Reverse(MyString),1) + 1))
    	End
    From	@vt_StringTable
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •