Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2014
    Posts
    6

    Unanswered: Deleting parts of a string - Please assist!

    The issue is that I need to delete parts of this string using SQL Server.

    This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".

    The title string reads:

    2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714

    I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"

    Any help you provide will be GREATLY appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What are the rules that help you figure out where to cut what from the string? Is it character count, patters, characters, data from another table, or something that Ihaven't guessed?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    6
    Thank u for responding.
    I'm open to any particular method that you feel is best. However I was thinking character count would be a good way...counting from left and then from right.

  4. #4
    Join Date
    Mar 2014
    Posts
    6

    re:

    Character count from left and then from right might be an effective way. But I'm open to anyway that you suggest. I've been trying to write this query for two days and haven't gotten anywhere!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps it would be clearer if you provided a good number of sample rows of varying formats as well as what result you'd like from each.

    This should help clear up things and will result in you receiving a better answer rather than a "best guess".
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2005
    Posts
    5
    If the left and right characters to be ommited are of fixed length then you can use below query.
    Replace the value with actual column name.

    select left(right('2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714',len('2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714')-9), len('2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714')-7)

  7. #7
    Join Date
    Mar 2014
    Posts
    6

    Re:

    Hey George,
    Theres so much sensitive data in this database, including more samples may infringe on that. What else specifically could I include?

  8. #8
    Join Date
    Mar 2014
    Posts
    6

    How about this?

    What do you think about something like this that someone posted?

    declare @test varchar(255)
    set @test = '2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714'

    select
    @test as origString,
    reverse(stuff(reverse(stuff(@test,1,9,'')),1,7,'') ) as newString

    Why are they using the word "@test" ? I really want to understand. Any clarifications or suggestions would be appreciated.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it can be mocked up data. Read this...

    http://www.dbforums.com/4527530-post3.html
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Mar 2014
    Posts
    6

    Example One

    What do you think about something like this that someone posted?

    declare @test varchar(255)
    set @test = '2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714'

    select
    @test as origString,
    reverse(stuff(reverse(stuff(@test,1,9,'')),1,7,'') ) as newString

    Why are they using the word "@test" and "origString & newString" ? Any clarifications or suggestions would be appreciated.

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
  •