SQL how to displayed result between to known phrases that will always been in the cell

Question

I have been reading up on this quite a bit, and am struggling to figure out how to do this.

Basically, I have a column that I have identified as Member_Type, and what displays in this column is a connection string that looks like the following:

Provider=SQLOLEDB.1;User ID=User;Password=Password;Data Source=Hostname;Initial Catalog=DESIRED_RESULT;Persist Security Info=TRUE/FALSE;Min Pool Size=5;Max Pool Size=x

Basically, in all of the connection strings, the ONLY thing I want to return in the results of the Member_Type column is everything AFTER the phrase "Initial Catalog=" but before ";Persist Security," as these phrases will appear in every result, and the variable and important information is the Initial Catalog database name that will be returned.

Since the User ID's, Passwords, and Data Sources will all vary at hundreds of different sites, a character count may not work for me.

Would anyone be able to tell me how to JUST get the result that appears between Catalog= and ;Persist?

Thank you!

Yikes. Why are you storing db passwords like this? It lacks security. — devlin carnate, Jul 11 '16 at 18:21
Possible duplicate of [How do I split a string so I can access item x?](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — devlin carnate, Jul 11 '16 at 18:27
Yeah don't even get me started on the password issue...that's a whole other conversation lol. — J. Wright, Jul 11 '16 at 18:47

score 0 · Answer 1 · answered Jul 11 '16 at 19:56

This should get you what you need:

DECLARE @variable VARCHAR(255) = 'Provider=SQLOLEDB.1;User ID=User;Password=Password;Data Source=Hostname;Initial Catalog=DESIRED_RESULT;Persist Security Info=TRUE/FALSE;Min Pool Size=5;Max Pool Size=x'

SELECT  SUBSTRING(STUFF(@variable ,1 ,CHARINDEX('Catalog=' ,@variable) + 7 ,'') ,0 ,
              CHARINDEX(';Persist' ,STUFF(@variable ,1 ,CHARINDEX('Catalog=' ,@variable) + 7 ,'')))

RESULT: DESIRED_RESULT

SQL how to displayed result between to known phrases that will always been in the cell

1 Answers1