pass a column instead of a string to mdq.Split?

Question

Post reply

pass a column instead of a string to mdq.Split?

drew.georgopulos

SSCarpal Tunnel

Points: 4931
More actions
April 24, 2012 at 7:20 am

#256958

instead of this
SELECT * FROM mdq.Split( N'http://www.microsoft.com/office/2007', N'./', 1, N'^\d+$', 0 );
how can i get to this?
SELECT * FROM mdq.Split( MyTable.MyColumn, N'.', 1, 0, 0 );
iow, i want pass a table and column that has a bunch of sentences in it and get a table with a sentence a row?
thanks a lot
drew

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It can't be done exactly they way you put it, but you can use CROSS APPLY to pass the column's values to the function:

SELECT *

FROM MyTable AS MT

CROSS APPLY mdq.Split(MyColumn, N'.', 1, 0, 0 ) AS CA;

Hope this helps,

Gianluca

-- Gianluca Sartori

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 2

drew.georgopulos

SSCarpal Tunnel

Points: 4931

April 24, 2012 at 11:07 am

#1478407

Wow!

thanks a ton

drew

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You're welcome.

Glad I could help.

-- Gianluca Sartori

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 4

i think i must have asked the wrong question...what i had envisoned was the contents being split on the delimiter down the rows...what i am getting is the entire field repeated as often as there are delimniters, so i need

Sentence 1

Sentence 2

Sentence N

but i am getting

Senteince1 Sentence2 Sentence N

thanks again for your time and attention

drew

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

What does your split function look like? Can you post that with some ddl and a couple rows of sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 6

the split function in inbuilt from MasterDataServices

http://msdn.microsoft.com/en-us/library/ee633810.aspx

the insticles look like this

/****** Object: UserDefinedFunction [mdq].[Split] Script Date: 04/24/2012 17:58:04 ******/

ALTER FUNCTION [mdq].[Split](@input [nvarchar](4000), @separators [nvarchar](10), @removeEmpty [bit], @tokenPattern [nvarchar](4000), @mask [tinyint])

RETURNS TABLE (

[Sequence] [int] NULL,

[Token] [nvarchar](4000) NULL,

[IsValid] [bit] NULL

) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[SplitWithCheck]

GO

a sample of the string to parse follows...i'm leary of posting more because of HIPAA, and depersonalizing the data for patient identity, attending physician, reviewer etc is huge...here is a sample of what needs to be broken out though.

MCO received a request for a bone growth stimulator. This request was reviewed by a Board-Certified Orthopedic surgeon. Mr. X injured his left ankle playing basketball on 01/29/2010. Treatment was a cast for 4 weeks, then physical therapy. Patient continues to have weakness and pain in the left ankle. MRI on 07/28/2010 reveals evidence of torn ligaments: deltoid, talofibular and fibulocalcaneal and an avulsion fracture of the fibula. The avulsion fracture is part of the torn ligaments of the ankle. If Mr. X has instability, which the physician may be considering with the continued weakness, the patient may require stress X-rays. If the talar tilt is greater than 13 degrees and anterior drawer is greater than 1 cm, then surgery is indicated as noted by DeLee, Drez, and Miller. Torn ligaments do not require bone growth stimulator. The avulsion fracture is related to the torn ligaments and is not a through-and-through fracture of the fibula. The request is not medically necessary because of the diagnosis of multiple ligament tears of the ankle, and a bone-growth stimulator is not the most appropriate level of service and is not an accepted medical practice procedure for this diagnosis. REFERENCES: DeLee and Drez, Orthopedic sports medicine, 3rd ed Miller and Sekiya, Sports Medicine

thanks very much

drew