Post reply

SQl 2014 Database records have bad data how to clear

  • March 4, 2019 at 10:20 am

    #381623

    SQL 2014 Cluster 2 Nodes on Windows 2012 R2 Server both are VMs on ESXI 6.5 Hosts
    all working well.

    I have a database that has bad data and I need t remove the bad data from each record.

    M:\Music\MP3MusicAlbums\Til Tuesday\Billboard Top 100 - 1985\01-Billboard Top 100 - 1985-68 Voices Carry.mp3<div style="display:none">cialis coupon <a href="http://redsoctober.com/page/coupons-for-cialis.aspx" rel="nofollow">cialis coupon</a> cialis coupon</div>

    I need to remove this from the record
    <div style="display:none">cialis coupon <a href="http://redsoctober.com/page/coupons-for-cialis.aspx" rel="nofollow">cialis coupon</a> cialis coupon</div>

    I have over 35000 records in this database

    The <div style is random and not on all records but many records have this.

    This database was migrated from an SQL 2008 Server some time ago

    I had a similar problem back then also

    I used this query to clear the records.

    updatedbo.recordings set RecordingTitle = left(RecordingTitle, CHARINDEX('<',RecordingTitle)-1) WHERECHARINDEX('<', RecordingTitle ) > 0

    Not sure if this will work on 2014 I tried it before and did not do anything.

    The database

    Has

    TrackID
    TrackTitle
    RecordingID
    TrackSequence
    TrackFileName

    TrackFileName is where it has the bad data

    Any ideas

    Thanks

  • March 4, 2019 at 3:17 pm

    #2023505

    just test your changes via a select query before you implement an actual update:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    IF OBJECT_ID('tempdb.[dbo].[#recording]') IS NOT NULL
    DROP TABLE [dbo].[#recording]
    GO
    SELECT 1 AS TrackID,
    'SomeTitle' AS TrackTitle,
    1 AS RecordingID,
    1 AS TrackSequence,
    TrackFileName = 'M:\Music\MP3MusicAlbums\Til Tuesday\Billboard Top 100 - 1985\01-Billboard Top 100 - 1985-68 Voices Carry.mp3<div style="display:none">cialis coupon <a href="http://redsoctober.com/page/coupons-for-cialis.aspx" rel="nofollow">cialis coupon</a> cialis coupon</div>'
    INTO #recording

    SELECT TrackFileName,
    Myfn.StartDiv,
    Myfn.EndDiv,
    SUBSTRING(TrackFileName,Myfn.StartDiv,(Myfn.EndDiv - Myfn.StartDiv) + 6),
    --STUFF can be used to remove a number of chars
    STUFF(TrackFileName,Myfn.StartDiv,(Myfn.EndDiv - Myfn.StartDiv) + 6,'')
    FROM #recording
    CROSS APPLY (SELECT CHARINDEX('<div',TrackFileName) AS StartDiv,CHARINDEX('</div>',TrackFileName) AS EndDiv) Myfn


    you were not clear about what is the bad data, the entire <div></div> or portions of it.

    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!

  • March 4, 2019 at 4:59 pm

    #2023512

    Thanks

    I got the records cleared now.

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

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