How to update a large text field

Question

Post reply

How to update a large text field

brickpack

SSCrazy

Points: 2702
More actions
June 5, 2009 at 9:41 am

#134782

Can someone help me update this text field? I need to update the Serial and MX records in this zone file. I have over 150 records to update. The Serial numbers are based on a date and are currently different. They'll need to be updated with todays date. The MX records are all the same and need to be updated to "filter.mydomain.net."
Example...
2009011501; Serial
-- Update to...2009060501; Serial
2008011501; Serial
-- Update to...2009060501; Serial
2007011501; Serial
-- Update to...2009060501; Serial
INMX10mdocmx.mydomain.net.
-- Update to..."INMX10filter.mydomain.net."
INMX 20 mdocmx.mydomain.net.
-- Update to..."INMX 20 filter.mydomain.net."
Here's a sample of the data...
IF OBJECT_ID('TempDB..#zone_files','U') IS NOT NULL
DROP TABLE #zone_files
CREATE TABLE [dbo].[#zone_files](
[domain] varchar(70),
[rid] char(3) NULL,
[zone] text NULL,
[moddate] datetime NULL,
[type] int NULL
)
INSERT INTO #zone_files
(domain, rid, zone, moddate, type)
SELECT 'mydomain.net','you','$TTL 7200
@INSOAns3.somedomain.com. postmaster.somedomain.com. (
2009011501; Serial
10800; Refresh after 3 hours
3600; Retry after 1 hour
604800; Expire after 1 week
86400 ); Minimum TTL of 1 day
INNSns3.somedomain.com.
INNSns4.somedomain.com.
IN A 123.123.123.123
; IN A 123.123.123.123
INMX10mdocmx.mydomain.net.
INMX 20 mdocmx.mydomain.net.
localhostINA127.0.0.1
;
popINCNAMEpop.somedomain.com.
mailINCNAMEmail.somedomain.com.
smtpINCNAMEsmtp.somedomain.com.
wwwINCNAME@
ftpINCNAME@
newsINCNAMEnews.somedomain.com.
','2009-01-15 01:05:30',2 UNION ALL
SELECT 'anotherdomain.net','big','$TTL 7200
@INSOAns3.somedomain.com. postmaster.somedomain.com. (
2008011501; Serial
10800; Refresh after 3 hours
3600; Retry after 1 hour
604800; Expire after 1 week
86400 ); Minimum TTL of 1 day
INNSns3.somedomain.com.
INNSns4.somedomain.com.
IN A 123.123.123.123
; IN A 123.123.123.123
INMX10mdocmx.anotherdomain.net.
INMX 20 mdocmx.anotherdomain.net.
localhostINA127.0.0.1
;
popINCNAMEpop.somedomain.com.
mailINCNAMEmail.somedomain.com.
smtpINCNAMEsmtp.somedomain.com.
wwwINCNAME@
ftpINCNAME@
newsINCNAMEnews.somedomain.com.
','2009-01-15 01:05:30',1 UNION ALL
SELECT 'yetanotherdomain.net','our','$TTL 7200
@INSOAns3.somedomain.com. postmaster.somedomain.com. (
2007011501; Serial
10800; Refresh after 3 hours
3600; Retry after 1 hour
604800; Expire after 1 week
86400 ); Minimum TTL of 1 day
INNSns3.somedomain.com.
INNSns4.somedomain.com.
IN A 123.123.123.123
; IN A 123.123.123.123
INMX10mdocmx.yetanotherdomain.net.
INMX 20 mdocmx.yetanotherdomain.net.
localhostINA127.0.0.1
;
popINCNAMEpop.somedomain.com.
mailINCNAMEmail.somedomain.com.
smtpINCNAMEsmtp.somedomain.com.
wwwINCNAME@
ftpINCNAME@
membersINCNAMEmembers.somedomain.com.
newsINCNAMEnews.somedomain.com.
','2009-01-15 01:05:30',2

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi dave

If your zone text has always the same syntax you can use this.

I used the spaces in your Serial line to replace the date part and "mdocmx." + domain to handle the new domain.

; WITH

cte ([domain], [rid], [zone], [moddate], [type]) AS

(

SELECT

domain,

rid,

CONVERT(VARCHAR(MAX), zone),

moddate,

type

FROM #zone_files

)

SELECT

REPLACE

(

STUFF(zone, CHARINDEX(' ', zone, 1) + DATALENGTH(' '), 8, '20090605'),

'mdocmx.' + domain,

'filter.' + domain

)

,*

FROM cte

Greets

Flo

brickpack SSCrazy Points: 2702 More actions · Answer 2

Thanks for the reply Flo!

Unfortunately...the white space in zone file can be tab characters or any given number of white spaces.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Okay, which criteria are assured?

* "01 ; Serial" after your date?

* " ; Serial" after your date?

* Line 3 for your date?

* Serial at the end of the line with your date?

Greets

Flo

brickpack SSCrazy Points: 2702 More actions · Answer 4

It will always be: * " ; Serial" with any given amount of spaces or tabs between the date, semicolon and "Serial."

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

dave b (6/5/2009)
It will always be: * " ; Serial" with any given amount of spaces or tabs between the date, semicolon and "Serial."

Do you have to keep those spaces and/or tabs?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi dave

This solution using PATINDEX should work.

; WITH

cte ([domain], [rid], [zone], [moddate], [type]) AS

(

SELECT

domain,

rid,

CONVERT(VARCHAR(MAX), zone),

moddate,

type

FROM #zone_files

)

SELECT

REPLACE

(

STUFF(zone, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%;%Serial%', zone), 8, '20090605'),

'mdocmx.' + domain,

'filter.' + domain

)

,*

FROM cte

Greets

Flo

brickpack SSCrazy Points: 2702 More actions · Answer 7

brickpack

SSCrazy

Points: 2702

June 10, 2009 at 5:43 pm

#1007716

Thanks Flo!

That worked great.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Florian Reischl

SSC-Dedicated

Points: 37301

June 11, 2009 at 3:47 am

#1007861

Thanks for the feedback! 🙂