String - how to find multiple occurences? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String - how to find multiple occurences?

Author

Topic

denali
Starting Member

2 Posts

Posted - 2009-05-12 : 00:23:56

I am trying to write a SQL script to do some string processing.
Basically, I have strings in this format:

"http://www.abc.com/isn/asp/images/InsertContractorNameDynamic.gif"

www.abc.com
www.abc.co.uk
www.abc.ca
* The links above may be used with or without http://

There are so many different ways that the link might be typed in by the user, so I want to format the string into this format:

"///isn/asp/images/InsertContractorNameDynamic.gif"

My problem is, the initial string may have multiple occurences of the link, how can I loop through the string to format each occurence into the format I want?

Here is my script - It does what I want for 1 occurence of "Contractor Name", but how do I do it for multiple occurences?

Create function dbo.dynamicnames ()
returns varchar(8000)
as
begin

declare @origstr nvarchar (500)
declare @tempstrC nvarchar (500)
declare @BeforeTxtPositionC smallint
declare @AfterTxtPositionC smallint
declare @BeforeTxtC nvarchar (500)
declare @AfterTxtC nvarchar (500)
declare @xC nvarchar (500)
declare @len1C smallint

set @origstr ='<P>Dear <IMG alt="Insert Contractor Name Dynamically" src="http://www.abc.com/asp/images/InsertContractorNameDynamic.gif">,</P> <P>Attn: <IMG height=25 alt="Insert User Last Name Dynamically" src="http://www.abc.com/asp/images/InsertLastNameDynamic.gif" width=115>, <IMG height=25 alt="Insert User First Name Dynamically" src="http://www.abc.com/asp/images/InsertFirstNameDynamic.gif" width=115></P> <P>Test MEssage</P>'

begin
set @len1C = len(@origstr)
set @BeforeTxtPositionC = charindex('"Insert Contractor Name Dynamically" src=', @origStr) + 40

set @AfterTxtPositionC = charindex('/asp/images/InsertContractorNameDynamic.gif"', @origStr)

set @BeforeTxtC = left(@origStr,@BeforeTxtPositionC)
set @AfterTxtC = substring(@origStr,@AfterTxtPositionC,@len1C)
set @tempstrC = @BeforeTxtC + '"//' + @AfterTxtC
set @xC = replace(@tempstrC, '<IMG alt="Insert Contractor Name Dynamically" src="///asp/images/InsertContractorNameDynamic.gif">', 'ContractorName')

return @xC
end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-12 : 10:41:51

post enough sample values that if we solved all of the examples you would be happy. Post it in a form so we can insert your sample data into a table var. Perhaps something like this (assuming you want 1 row per input. It's not clear to me what you mean by "multiple occurences". If you may need multiple rows of output for your multiple occurences then post expected output accordingly.


declare @t table (origString varchar(2000), desiredOutput varchar(2000))
insert @t 
select 'http://www.abc.com/isn/asp/images/InsertContractorNameDynamic.gif', '///isn/asp/images/InsertContractorNameDynamic.gif' union all
select 'www.abc.com', 'whatever this should be' union all
select 'www.abc.co.uk', 'whatever this should be'

OUTPUT:
origString                                                                desiredOutput
-----------------------------------------------------------------------------------------------------------------------------
http://www.abc.com/isn/asp/images/InsertContractorNameDynamic.gif         ///isn/asp/images/InsertContractorNameDynamic.gif
www.abc.com                                                               whatever this should be
www.abc.co.uk                                                             whatever this should be

Be One with the Optimizer
TG

denali
Starting Member

2 Posts

Posted - 2009-05-12 : 12:58:19

When I said multiple occurences, this is an example of what I meant. It has multple occurences of the link that I want to format.

@origstr ='<P>Dear <IMG alt="Insert Contractor Name Dynamically" src="http://www.isnetworld.com/isn/asp/images/InsertContractorNameDynamic.gif">,</P> <P>Attn: <IMG alt="Insert Contractor Name Dynamically" src="http://www.isnetworld.com/isn/asp/images/InsertContractorNameDynamic.gif">, <IMG alt="Insert Contractor Name Dynamically" src="http://www.isnetworld.com/isn/asp/images/InsertContractorNameDynamic.gif"></P> <P>Test MEssage</P>'

Desired output:

<P>Dear Contractor Name,</P>
<P>Attn: Contractor Name
, Contractor Name</P> <P>Test MEssage</P

Please note that the origstr can vary, so I can't hardcord the position of any char. But the characters within the IMG tag will not change.

<IMG alt="Insert Contractor Name Dynamically" src="http://www.isnetworld.com/isn/asp/images/InsertContractorNameDynamic.gif">

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:24:01

cant you just do this since pattern is static?

REPLACE(@origstr,'<IMG alt="Insert Contractor Name Dynamically" src="http://www.isnetworld.com/isn/asp/images/InsertContractorNameDynamic.gif">','Contractor Name')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources