Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Replace question...

    I have a text column with some text that needs to be removed.
    Here is an example:

    body_text = 'Title=Title
    Subtitle=Subtitle
    Subtitle2=subtitle2

    story content here, more content
    more content'

    is there a way to strip out the title=title,etc. up to the beginning of the story content?

    - Thanks...

  2. #2
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Re: Replace question...

    Can you explain what you whant a little more.

    ______________
    Paulo Gonçalves

  3. #3
    Join Date
    Nov 2002
    Posts
    42
    Sure.

    I have a field in my table that contains an article. These articles were converted over from another system, so when I converted them I appended the article's title, subtitle, subtitle2, and subtitle3 to the beginning of the article so the editors could have access to that information. The conversion and cleanup process have been completed and I now need to strip the title/subtitle information OUT of the article while leaving the article text intact. Is this possible?

  4. #4
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    If i understand write what you need is to clean the information in two fields of each record.
    If it's this you just use the Update instruction to update those fileds with an empty value.

    ______________
    Paulo Gonçalves

  5. #5
    Join Date
    Nov 2002
    Posts
    42
    The problem is that ALL the information is in one field. The title/subtitle/subtitle2/subtitle3 AND article content are all in one field. Is it possible to clean the title/subtitle information while leave the article content intact?

  6. #6
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    Ok, in that case first Slect the record to be cleaned pass the entire row to a variable and then cut the information from position the first charecter (0) until the secont / (if they are separated by /).
    But the best thing you could do it's import that information to a new table, creating a colune for each field.

    ______________
    Paulo Gonçalves

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I don't know if I have your exact scenario in mind but maybe this will help:

    Code:
    -- setup a table
    create table #tmp(RecordID int not null identity(1,1), Article text)
    
    -- populate the table with some data
    insert into #tmp (Article)
    values(
    'body_text = ''Title=Title
    Subtitle=Subtitle
    Subtitle2=subtitle2
    
    story content here, more content
    more content' )
    
    insert into #tmp (Article)
    values(
    'body_text = ''Title=Title
    Subtitle=Subtitle
    Subtitle2=subtitle2
    
    Big story content here, more content
    more content' + char(13) + char(10) + replicate('*&',3800) )
    
    -- select the two rows just entered, I did it this way to help seperate the text.
    select cast(Article as varchar(100)) From #Tmp where RecordID = 1
    select cast(Article as varchar(100)) From #Tmp where RecordID = 2
    
    -- Declare two variable, One to hold the characters used to define "NewLine"
    -- and one to hold the patter we will be looking for
    declare @Pattern varchar(100), @NewLine varchar(2)
    set @NewLine = char(13) + char(10)
    set @Pattern = 'body_text%' + @NewLine +  @NewLine + '%'
    
    -- Essentially flip the text and pattern backwards and look for the first occurence
    -- of pattern.
    select RecordID
         , patindex(reverse(@Pattern),reverse(cast(Article as varchar(8000)))) as 'Right most ende of test to strip'
         , datalength(Article) as 'Text length'
         , Substring(Article,datalength(Article) - patindex(reverse(@Pattern),reverse(cast(Article as varchar(8000)))) + 2, 8000)
      from #Tmp 
     where patindex(reverse(@Pattern),reverse(cast(Article as varchar(8000)))) > 0
    drop table #tmp
    Of course this us untested for your environment. You will need to evaluate this to see if it meets your needs.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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