Blank the data in row from Position 50-74

Question

Post reply

Blank the data in row from Position 50-74

Rakesh.Chaudhary

SSC Eights!

Points: 901
More actions
September 13, 2012 at 5:57 am

#261792

Hi,
I want to blank the data in below line where row start will be always ZY05034 ,Then I have to replace data in row from 50-74 as Blank
This Row is part of File
ZY05034000002345678912345678901100000000000889758 5668550000000

Viewing 9 posts - 1 through 8 (of 8 total)

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

Rakesh.Chaudhary SSC Eights! Points: 901 More actions · Answer 1

Currently I have started as

Case

CASE

WHEN CHARINDEX('ZY050340',Data)>0

FRom XYZ_TB

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

not sure if I fully understand your requirements...but does this work?

UPDATE XYZ_TB

SET data = LEFT( data , 49 )

WHERE LEFT( data , 8 ) = 'ZY050340';

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ant-Green SSC Guru Points: 113509 More actions · Answer 3

I think the requirements is to strip out anything between positions 50 and 74 and replace it with a blank space

So

ZY0503400000234567891234567890110000000000088975812345678901234567890123455668550000000

Becomes

ZY05034000002345678912345678901100000000000889758 5668550000000

Something like this

SELECT

LEFT('ZY0503400000234567891234567890110000000000088975812345678901234567890123455668550000000',49)

+' '+

RIGHT('ZY0503400000234567891234567890110000000000088975812345678901234567890123455668550000000',

LEN('ZY0503400000234567891234567890110000000000088975812345678901234567890123455668550000000')-74)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

STUFF() is perfect for this:

SELECT

Data,

ChangedData = STUFF(Data,50,25,SPACE(25))

FROM ( -- note: '[' is position 49 and ']' is position 75

SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'

) d

WHERE LEFT(Data,8) = 'ZY050340'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ChrisM@Work (9/13/2012)
STUFF() is perfect for this:
SELECT
Data,
ChangedData = STUFF(Data,50,25,SPACE(25))
FROM ( -- note: '[' is position 49 and ']' is position 75
SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'
) d
WHERE LEFT(Data,8) = 'ZY050340'

Or this:

SELECT

Data,

ChangedData = STUFF(Data,50,25,SPACE(25))

FROM ( -- note: '[' is position 49 and ']' is position 75

SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'

) d

WHERE

Data LIKE 'ZY050340%'; -- More likely to use an index on Data if one exists

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Lynn Pettis (9/13/2012)
ChrisM@Work (9/13/2012)
STUFF() is perfect for this:
SELECT
Data,
ChangedData = STUFF(Data,50,25,SPACE(25))
FROM ( -- note: '[' is position 49 and ']' is position 75
SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'
) d
WHERE LEFT(Data,8) = 'ZY050340'
Or this:
SELECT
Data,
ChangedData = STUFF(Data,50,25,SPACE(25))
FROM ( -- note: '[' is position 49 and ']' is position 75
SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'
) d
WHERE
Data LIKE 'ZY050340%'; -- More likely to use an index on Data if one exists

Quite right too - good catch Lynn. "Incremental development".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rakesh.Chaudhary SSC Eights! Points: 901 More actions · Answer 7

Thanks to all for the replies. The solution given by Lynn and Chris was Perfect....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Rakesh.Chaudhary (9/13/2012)
Thanks to all for the replies. The solution given by Lynn and Chris was Perfect....

All the work was Chris's, all I did was offer a slight change to the code, but thanks for the feedback as that is always good.