First split the string at ';' .. You can find many Split functions online. Use one that splits it into a table.
Following Code is from: How to split string using delimiter char using T-SQL?
CREATE FUNCTION [dbo].[Split]
(
@String varchar(max)
,@Delimiter char =';' -- default value
)
RETURNS @Results table
(
Ordinal int
,StringValue varchar(max)
)
as
begin
set @String = isnull(@String,'')
set @Delimiter = isnull(@Delimiter,'')
declare
@TempString varchar(max) = @String
,@Ordinal int = 0
,@CharIndex int = 0
set @CharIndex = charindex(@Delimiter, @TempString)
while @CharIndex != 0 begin
set @Ordinal += 1
insert @Results values
(
@Ordinal
,substring(@TempString, 0, @CharIndex)
)
set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)
set @CharIndex = charindex(@Delimiter, @TempString)
end
if @TempString != '' begin
set @Ordinal += 1
insert @Results values
(
@Ordinal
,@TempString
)
end
return
end
assuming the order is always the same, split each of the resutls at the '='.
take the right part of every string (the length of the remaining string after '=')..
et voilà, you have every property with its value.
-- EDIT: With the Split Function from above:
DECLARE @TestConnectionString varchar(255) = 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
create table #result
(
property varchar(255),
Value varchar(255)
)
create table #tmp
(
Property varchar(255)
)
create table #tmp2
(
Value varchar(255)
)
insert into #tmp
select * from split(@TestConnectionString, ';')
--select * from #tmp
/* Sclaufe */
declare @id varchar(255)
DECLARE a_coursor CURSOR FOR
select property from #tmp
OPEN a_coursor;
FETCH NEXT FROM a_coursor into @id;
WHILE @@FETCH_STATUS = 0
BEGIN
-- select @id
insert into #tmp2
select * from Split(@id, '=')
FETCH NEXT FROM a_coursor
INTO @id
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;
select * from #tmp2
/* Sclaufe */
declare @id2 varchar(255)
declare @oldid varchar(255)
declare @count int
set @count = 1
DECLARE a_coursor CURSOR FOR
select value from #tmp2
OPEN a_coursor;
FETCH NEXT FROM a_coursor into @id2;
WHILE @@FETCH_STATUS = 0
BEGIN
print @id2
if @count % 2 <> 0
begin
insert into #result
select @id2, ''
set @oldid = @id2
end
else
begin
update #result
set Value = @id2
where property = @oldid
end
set @count = @count + 1
FETCH NEXT FROM a_coursor
INTO @id2
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;
select * from #result
drop table #tmp
drop table #tmp2
drop table #result
The result will be in the #ressult table:
╔═══════════════════════╦═════════════════╗
║ property ║ Value ║
╠═══════════════════════╬═════════════════╣
║ Data Source ║ 123.45.67.890 ║
║ User ID ║ TestUser ║
║ Password ║ TestPassword ║
║ Initial Catalog ║ TestCatalogName ║
║ Provider ║ SQLNCLI11.1 ║
║ Persist Security Info ║ True ║
║ Auto Translate ║ False ║
╚═══════════════════════╩═════════════════╝
EDIT: Or you can create a stored procedure:
if exists (select 1 from sysobjects where name = 'getvalue2' and type = 'P')
begin
drop procedure getvalue2
print 'Procedure: getvalue2 deleted ...'
end
go
/*
exec getvalue2 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
*/
create procedure [dbo].[getvalue2]
( @TestConnectionString varchar(255))
as
begin
--= 'Data Source=123.45.67.890;User ID=TestUser;Password=TestPassword;Initial Catalog=TestCatalogName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;'
create table #result
(
property varchar(255),
Value varchar(255)
)
create table #tmp
(
firstrun varchar(255)
)
create table #tmp2
(
secondrun varchar(255)
)
insert into #tmp
select * from split(@TestConnectionString, ';')
--select * from #tmp
declare @id varchar(255)
DECLARE a_coursor CURSOR FOR
select firstrun from #tmp
OPEN a_coursor;
FETCH NEXT FROM a_coursor into @id;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tmp2
select * from Split(@id, '=')
FETCH NEXT FROM a_coursor
INTO @id
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;
declare @id2 varchar(255)
declare @oldid varchar(255)
declare @count int
set @count = 1
DECLARE a_coursor CURSOR FOR
select secondrun from #tmp2
OPEN a_coursor;
FETCH NEXT FROM a_coursor into @id2;
WHILE @@FETCH_STATUS = 0
BEGIN
print @id2
if @count % 2 <> 0
begin
insert into #result
select @id2, ''
set @oldid = @id2
end
else
begin
update #result
set Value = @id2
where property = @oldid
end
set @count = @count + 1
FETCH NEXT FROM a_coursor
INTO @id2
END;
CLOSE a_coursor;
DEALLOCATE a_coursor;
select * from #result
end
have fun,
You're wellcome = )
