Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL -- RegEx (or something)

Posted on 2012-03-16
10
Medium Priority
?
525 Views
Last Modified: 2012-03-20
Change "select ExtensionSettings from Subscriptions"
so it only extracts "test3@test.com;test2@test.com"
on my 500+ results instead of getting the below ?

<ParameterValues><ParameterValue><Name>TO</Name><Value>test3@test.com;test2@test.com</Value></ParameterValue>
<ParameterValue><Name>ReplyTo</Name><Value>test@test.com</Value></ParameterValue>
<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue>
<ParameterValue><Name>Subject</Name><Value>EOM -- @ReportName</Value>
0
Comment
Question by:finance_teacher
10 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 37729142
select ExtensionSettings from Subscriptions where ExtensionSettings = 'test3@test.com;test2@test.com'
0
 

Author Comment

by:finance_teacher
ID: 37729267
That get the entire ExtensionSettings column, I only want the a piece of it.

Like a SUBSTRING, but based on a REGEX (or something else).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37731669
Why not use Xml Data Type Methods for this?  That would be far more precise than using something like PATINDEX()
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:finance_teacher
ID: 37733568
I read http://learnsqlserver.in/6/Xml-Data-Type-Methods.aspx, but have issues.

Please post an example solution.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37733787
Here you go:
DECLARE @MyXml xml =
'<ParameterValues>
	<ParameterValue>
		<Name>TO</Name>
		<Value>test3@test.com;test2@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>test@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>RenderFormat</Name>
		<Value>EXCEL</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>Subject</Name>
		<Value>EOM -- @ReportName</Value>
	</ParameterValue>
</ParameterValues>'

SELECT	t.c.value('Value[1]', 'varchar(50)')
FROM	@MyXml.nodes('ParameterValues/ParameterValue') T(C)

Open in new window

0
 

Author Comment

by:finance_teacher
ID: 37733955
My database table, subscriptions, has 500+ rows, how can I get your "DECLARE @MyXml xml..." to do something like "DECLARE @MyXml xml...select ExtensionSettings from Subscriptions..." ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37736105
Like this:
SELECT  s.*,
        t.c.value('Value[1]', 'varchar(50)') ExtensionSettings
FROM    subscriptions s
        CROSS APPLY s.MyXml.nodes('ParameterValues/ParameterValue') T (C)

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 total points
ID: 37736122
This is how I tested it:

DECLARE @subscriptions TABLE (MyXml xml)
INSERT @subscriptions (MyXml) VALUES (
'<ParameterValues>
	<ParameterValue>
		<Name>TO</Name>
		<Value>test3@test.com;test2@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>test@test.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>RenderFormat</Name>
		<Value>EXCEL</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>Subject</Name>
		<Value>EOM -- @ReportName</Value>
	</ParameterValue>
</ParameterValues>')

SELECT  s.*,
        t.c.value('Value[1]', 'varchar(50)') ExtensionSettings
FROM    @subscriptions s
        CROSS APPLY s.MyXml.nodes('ParameterValues/ParameterValue') T (C)

Open in new window

0
 
LVL 12

Assisted Solution

by:Ken Selvia
Ken Selvia earned 400 total points
ID: 37739584
Credit to ACPerkins, but here is some code that should work with your SQL Report Server subscriptions table;

SELECT  Description, t.c.value('Value[1]', 'varchar(50)') ExtensionSettings        
FROM    
( select CAST(extensionsettings AS XML) ExtensionSettings, Description from subscriptions ) s
CROSS APPLY s.ExtensionSettings.nodes('/ParameterValues/ParameterValue') T (C)
WHERE t.c.value('Name[1]', 'varchar(50)') = 'TO'

Open in new window

0
 

Author Comment

by:finance_teacher
ID: 37741846
Ok, I changed the below from "varchar(50)" to "varchar(500)" and it works.

SELECT  t.c.value('Value[1]', 'varchar(500)') ExtensionSettings        
FROM    
( select CAST(extensionsettings AS XML) ExtensionSettings, Description from subscriptions ) s
CROSS APPLY s.ExtensionSettings.nodes('/ParameterValues/ParameterValue') T (C)
WHERE t.c.value('Name[1]', 'varchar(500)') = 'TO'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question