String Extraction

Question

String Extraction

Chris Kitchen

Hall of Fame

Points: 3090
More actions
October 26, 2005 at 2:36 am

#169561

Hi,
I have a table with one column, and 5 rows ...

ksjdfhiuearhfkrferfSERVERNAMEeruwerwnerwer
ewruowieurowiueroiwuerSERVERNAMEweiyqwieuqwebqne
qweuyqwerSERVERNAMEweruwerwbnermbervbrehwiurhweiuSERVERNAMEswerwerwenmrbewr
SERVERNAMEwerwerbwmnerbwouihlajsjlakdjsidu
wiejweSERVERNAMEteguregttbgSERVERNAMErhtuererhbgf

I need to extract only the text SERVERNAME from each row and place it into a temporary table.

How can I do this?

Thanks in advance.
www.sqlAssociates.co.uk

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Is there anything in each rows that signifies where the servername starts and ends? (I assume that it's not as the example where it seems to be all the same, and is known beforehand?)

Point is, we need 'true' examples that follow the actual charachteristics and behaviours of your data.

/Kenneth

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

Hi,

I can't post the actual server names, the server name is always 18 characters long.

I was hoping to test the code using SERVERNAME and then put the code into a stored procedure and then pass the SERVERNAME in as a variable.

Thanks.

www.sqlAssociates.co.uk

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

It's ok, you can replace sensitive info with something else, the thing is - what traits in your data would indicate to programmed code that 'this is a servername'..?

From the example all I can see is one single string of random chars and att different positions a pattern of chars that is 'SERVERNAME'. Question is, do you know which 'SERVERNAME' to look for at compiletime, or is it unknown until runtime?

Thinking about it a bit, perhaps you could explain a bit more about the situation? Like the circumstances around these 5 rows and the temptable, what triggers this action and what's the purpose?

/Kenneth

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 4

Hi,

Basically when we restore a database from another server we need to check a certain table in the database (this table tells the application which database server to use).

Therefore, when we restore the database to another server we need to change the servername so that the application points at the server we have just restored the database to, rather than the server we backed the database up from.

Server specific information (ie. the server name) is only held in one table and in one column in the database.

The characteristics of the server name are ...

1. The first two letters are always AB or CD.

2. The servername is always 18 characters long and the 16th character is always \

Thanks for your help, its much appreciated.

www.sqlAssociates.co.uk

vadba SSChampion Points: 11133 More actions · Answer 5

Do you know both the old server name (source of the backup) and the new server name (target server to which the database was restored)?

If so, will a simple REPALCE do the job?

CREATE PROCEDURE uspChangeServerName

(

@oldServer varchar(18)

, @newServer varchar(18)

)

AS

UPDATE ServerInfo

SET info = Replace(info, @oldServer, @newServer)

GO

EXEC uspChangeServerName 'SERVERNAME', 'AB-NEWSERVER123/##'

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 6

Hi,

Thanks for that, I do know the new server name but I don't know the old server name.

www.sqlAssociates.co.uk

vadba SSChampion Points: 11133 More actions · Answer 7

Here's a test senario I ran that works for the test data you provided. I changed SERVERNAME to AB1234567890123\XX because this solution depends on the old server name characteristics being exactly as you stated.

/*

I was hoping to test the code using SERVERNAME

and then put the code into a stored procedure

and then pass the SERVERNAME in as a variable.

The characteristics of the server name are ...

1. The first two letters are always AB or CD.

2. The servername is always 18 characters long

3. The 16th character is always a backslash ('\')

*/

DROP TABLE ServerInfo

GO

DROP PROC uspChangeServerName

Go

CREATE TABLE ServerInfo

(

info varchar(100)

)

SET NOCOUNT ON

-- Using AB1234567890123\XX as the old server name

INSERT ServerInfo (info) VALUES ('ksjdfhiuearhfkrferfAB1234567890123\XXeruwerwnerwer')

INSERT ServerInfo (info) VALUES ('ewruowieurowiueroiwuerAB1234567890123\XXweiyqwieuqwebqne')

INSERT ServerInfo (info) VALUES ('qweuyqwerAB1234567890123\XXweruwerwbnermbervbrehwiurhweiuAB1234567890123\XXswerwerwenmrbewr')

INSERT ServerInfo (info) VALUES ('CD1234567890123\XXwerwerbwmnerbwouihlajsjlakdjsidu')

INSERT ServerInfo (info) VALUES ('wiejweCD1234567890123\XXteguregttbgCD1234567890123\XXrhtuererhbgf')

SET NOCOUNT OFF

GO

CREATE PROCEDURE uspChangeServerName

(

@newServer varchar(18)

)

AS

WHILE ( SELECT Count(*) FROM ServerInfo WHERE PatIndex('%AB_____________\__%', info) > 0 ) > 0

BEGIN

UPDATE ServerInfo

SET info = Stuff(info, PatIndex('%AB_____________\__%', info), 18, @newServer)

WHERE PatIndex('%AB_____________\__%', info) > 0

END

WHILE ( SELECT Count(*) FROM ServerInfo WHERE PatIndex('%CD_____________\__%', info) > 0 ) > 0

BEGIN

UPDATE ServerInfo

SET info = Stuff(info, PatIndex('%CD_____________\__%', info), 18, @newServer)

WHERE PatIndex('%CD_____________\__%', info) > 0

END

GO

EXEC uspChangeServerName 'AB-NEWSERVERXXX/**'

SELECT * FROM ServerInfo

vadba SSChampion Points: 11133 More actions · Answer 8

Sorry, I just updated my previous post, left out the part that handles 'CD'

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 9

Thanks for that, its greatly appreciated, I'll get it tested and let you know.

Chris.

www.sqlAssociates.co.uk

RyanRandall SSChampion Points: 13623 More actions · Answer 10

Aw, I hate it when that happens. You spend time on a solution, only to see that someone's beat you to it!

Oh well, here's what I did anyway (not exactly vastly different from mkeast)...

CREATE TABLE #ServerInfo (col1 varchar(100))

INSERT INTO #ServerInfo

SELECT 'ksjdfhiuearhfkrferfABEXAMPLESERV01\XXeruwerwnerwer' UNION ALL

SELECT 'ewruowieurowiueroiwuerCDEXAMPLESERV02\XXweiyqwieuqwebqne' UNION ALL

SELECT 'qweuyqwerABEXAMPLESERV03\XXweruwerwbnermbervbrehwiurhweiuABEXAMPLESERV01\XXswerwerwenmrbewr' UNION ALL

SELECT 'CDEXAMPLESERV04\XXwerwerbwmnerbwouihlajsjlakdjsidu' UNION ALL

SELECT 'wiejweABEXAMPLESERV01\XXteguregttbgCDEXAMPLESERV02\XXrhtuererhbgf'

DECLARE @find varchar(30)

SET @find = '%[AC][BD]_____________\__%'

WHILE EXISTS (SELECT * FROM #ServerInfo WHERE patindex(@find, col1) > 0)

UPDATE #ServerInfo SET col1 = CASE WHEN PATINDEX(@find, col1) = 0 THEN Col1 ELSE stuff(col1, patindex(@find, col1), 18, '*') END FROM #ServerInfo

UPDATE #ServerInfo SET col1 = REPLACE(col1, '*', 'AB-NEWSERVER123\##') FROM #ServerInfo

SELECT * FROM #ServerInfo

DROP TABLE #ServerInfo

Regards,

Ryan

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.