Prescient
Prescient

Reputation: 1061

Remove text from column in sql database

I need to remove some text from a sql db in a metadata colum that's taking up too much space and isn't necessary.

ImageDescription:               Make:NIKON     Model:COOLPIX L26     Orientation:1     XResolution:300     YResolution:300     ResolutionUnit:2     Software:COOLPIX L26    V1.0     DateTime:2013:01:05 05:43:12     YCbCrPositioning:2     ExposureTime:0.04     FNumber:3.2     ExposureProgram:2     ISOSpeedRatings:200     ExifVersion:3836042731,664497658,2489535484,2327246609     DateTimeOriginal:2013:01:05 05:43:12     DateTimeDigitized:2013:01:05 05:43:12     ComponentsConfiguration:185856,59901696,256,1280     CompressedBitsPerPixel:2     ExposureBiasValue:0     MaxApertureValue:3.4     MeteringMode:5     LightSource:0     Flash:24     FocalLength:4.6     UserComment:0,0,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,2105376,16973830,65539,393216,18481972,65541,62128128,18546688,65541,62652416,19398656,65539,131072,33685414,65540,63176704,33685504,65540,223674368,0,19660800,65536,19660800,65536,3640590336,2214648831,202051584,269093902,302910734,403902481,370678312,590419990,975707429,960249139,1077360691,1078877256,927291204,1366118456,1734500183,1295935336,1685092721,1734696056,303104355,404035602,790239791,1110983267,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,3237962595,528640,60817528,33562881,285409553,29687553,16777378,16843013,65793,0,16777216,84148994,151521030,1051402,50528514,84083714,263173,24969472,67109634,554829073,319177009,570909009,2167542897,587768209,365015362,619762002,2188534323,387320329,622467352,690497318,909456426,976828471,1178944579,1246316615,1448432723,1515804759,1717920867,1785292903,1987409011     FlashpixVersion:1687135923,3231872644,1301805154,2554547069     ColorSpace:1     PixelXDimension:640     PixelYDimension:480     FileSource:3     SceneType:1     CustomRendered:0     ExposureMode:0     WhiteBalance:0     DigitalZoomRatio:0     FocalLengthIn35mmFilm:26     SceneCaptureType:0     GainControl:1     Contrast:0     Saturation:0     Sharpness:0     SubjectDistanceRange:0   

I want to get rid of the Usercomment part of that text. So far I have this that will remove anything left of the usercomment.

UPDATE Document
SET MetaData = LEFT(MetaData, CHARINDEX('UserComment:', MetaData) -1)
WHERE MetaData IS NOT NULL
AND MetaData like '%UserComment:%'
AND DocumentId = '480024' 

But I just want to get rid of the usercomment and not anything before or after that.

here is a sample of just the UserComment, it seems to not have any spaces.

      UserComment:0,0,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,538976288,2105376,16973830,65539,393216,18481972,65541,62128128,18546688,65541,62652416,19398656,65539,131072,33685414,65540,63176704,33685504,65540,223674368,0,19660800,65536,19660800,65536,3640590336,2214648831,202051584,269093902,302910734,403902481,370678312,590419990,975707429,960249139,1077360691,1078877256,927291204,1366118456,1734500183,1295935336,1685092721,1734696056,303104355,404035602,790239791,1110983267,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,1667457891,3237962595,528640,60817528,33562881,285409553,29687553,16777378,16843013,65793,0,16777216,84148994,151521030,1051402,50528514,84083714,263173,24969472,67109634,554829073,319177009,570909009,2167542897,587768209,365015362,619762002,2188534323,387320329,622467352,690497318,909456426,976828471,1178944579,1246316615,1448432723,1515804759,1717920867,1785292903,1987409011 

Any help will be appreciated.

Upvotes: 0

Views: 739

Answers (1)

asafrob
asafrob

Reputation: 1858

you can use charindex to find where the user comment start and where it ends

http://msdn.microsoft.com/en-us/library/ms186323.aspx

After that use Left & Right functions to create the string you want to keep

http://msdn.microsoft.com/en-us/library/ms177601.aspx

http://msdn.microsoft.com/en-us/library/ms177532.aspx

select 
    CHARINDEX('UserComment:', MetaData),
    CHARINDEX(' ', MetaData, CHARINDEX('UserComment:', MetaData)),
    LEFT(aaa, CHARINDEX('UserComment:', MetaData)-1) + RIGHT(MetaData, LEN(MetaData) - CHARINDEX('     ', MetaData, CHARINDEX('UserComment:', MetaData))) 
 from dbo.MetaData

Upvotes: 2

Related Questions