Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to parse connection strings on a column in SQL Server 2008 Expand / Collapse
Author
Message
Posted Thursday, November 28, 2013 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:43 PM
Points: 26, Visits: 352
I want to extract database names from a varchar column storing connection strings.

A sample value is; 'server=SVR1;database=DB1;uid=user1;pwd=pass1;enlist=true;'

Each record might have a slightly different connection string format; Uid before Database, or no Enlist etc. 'server=SVR1;uid=user1;database=DB1;pwd=pass1;'

I have a few parsing scripts but they are not useful for this specific extraction.

Basically, I want the string starts after 'database=' and ends at the following ';'.

Thanks,

Kuzey
Post #1518392
Posted Thursday, November 28, 2013 11:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:43 PM
Points: 26, Visits: 352
Found it.

SELECT
substring(substring(dbconnection,CHARINDEX('Database=',dbconnection)+9, LEN(dbconnection)),1,CHARINDEX(';',substring(dbconnection,CHARINDEX('Database=',dbconnection)+9, LEN(dbconnection)))-1)
FROM Connections


Thanks,

Kuzey
Post #1518396
Posted Thursday, November 28, 2013 5:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
You can expose pretty much any parameter you want regardless of position using a pattern-based string splitter like this:

WITH SampleData (ID, ConnectionString) AS
(
SELECT 1, 'server=SVR1;database=DB1;uid=user1;pwd=pass1;enlist=true'
UNION ALL SELECT 2, 'database=DB1;server=SVR1;uid=user1;pwd=pass1;enlist=true'
UNION ALL SELECT 3, 'server=SVR1;uid=user1;pwd=pass1;database=DB1;enlist=true'
),
Params AS
(
SELECT *
FROM SampleData
CROSS APPLY PatternSplitCM(ConnectionString, '[;=]')
WHERE [Matched]=0
)
SELECT ID, dbname=Item
FROM Params a
WHERE ItemNumber =
(
SELECT 2+ItemNumber
FROM Params b
WHERE a.ID = b.ID AND b.Item = 'database'
);


PatternSplitCM is a utility function that can be found in the 4th article in my signature links.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1518419
Posted Thursday, November 28, 2013 6:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:43 PM
Points: 26, Visits: 352
Great, Thanks.
Post #1518429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse