Post reply

Using left and charindex to parse string , getting rid of rest of data

  • June 16, 2015 at 2:15 pm

    #309281

    I'm confused, as I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins

    update MyTable

    set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1

    that totally works.

    update MyTable

    set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2

    gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error

    ??

    arch

  • June 16, 2015 at 2:24 pm

    #1805890

    there's no guarantee that EVERY row contains the search term, so you need to include that check in your WHERE.

    update MyTable

    set Description = LEFT(Description(CHARINDEX('<',Description)-1))

    where CHARINDEX('<',Description) > 0

    AND myid >= 2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 16, 2015 at 2:36 pm

    #1805895

    Thanks, that rocks :-D:-D

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply