Fields

Ben
CREATE FUNCTION dbo.TsqlSplit

(@List As varchar(8000))

RETURNS @Items table (Item varchar(8000) Not Null)

AS

BEGIN

DECLARE @Item As varchar(8000), @Pos As int

WHILE DATALENGTH(@List)>0

BEGIN

SET @Pos=CHARINDEX(',',@List)

IF @Pos=0 SET @Pos=DATALENGTH(@List)+1

SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))

IF @Item<>'' INSERT INTO @Items SELECT @Item

SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)

END

RETURN

END

GO

/* Usage example */

SELECT t1.*

FROM TsqlSplit('10428,10429') AS t1


declare @inList varchar(50)

set @inList='10428,10429'

select od.* from [order details] od

INNER JOIN

(SELECT Item

FROM dbo.TsqlSplit(@InList)) As t

ON od.orderid = t.Item





"Ben Watts" wrote in message
news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>I have some simple SQL code below.
> SELECT DAT_V_MEDHIST.COMMENT01
> FROM DAT_V_MEDHIST LEFT OUTER JOIN
> STY_STUDY ON DAT_V_MEDHIST.STUDY = STY_STUDY.NAME
> I need to take the comment filed and split it into 3 separate fields. The
> data in the comment field looks like this:
> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>
> Basically, the first field will be the number which is followed by the
> separator ¬. Then the second field follows that ¬ and goes until the next
> ¬.
> The third field starts after the second ¬ and and goes to the | and then
> it
> all starts over. I am not good enough in SQL to figure this one out. Is
> their any genious out there that can help me out with this? Thanks in
> advance!

Thanks Uri,

One more question, as I am not very good at SQL yet, do I need to fill
anything in to the code you sent me or just put it in the dataset? Thanks
again!

"Uri Dimant" wrote in message
news:O6USx$8SKHA.4704atTK2MSFTNGP02dotphx.gbl...
> Ben
> CREATE FUNCTION dbo.TsqlSplit
>
> (@List As varchar(8000))
>
> RETURNS @Items table (Item varchar(8000) Not Null)
>
> AS
>
> BEGIN
>
> DECLARE @Item As varchar(8000), @Pos As int
>
> WHILE DATALENGTH(@List)>0
>
> BEGIN
>
> SET @Pos=CHARINDEX(',',@List)
>
> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>
> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>
> IF @Item<>'' INSERT INTO @Items SELECT @Item
>
> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>
> END
>
> RETURN
>
> END
>
> GO
>
> /* Usage example */
>
> SELECT t1.*
>
> FROM TsqlSplit('10428,10429') AS t1
>
>
> declare @inList varchar(50)
>
> set @inList='10428,10429'
>
> select od.* from [order details] od
>
> INNER JOIN
>
> (SELECT Item
>
> FROM dbo.TsqlSplit(@InList)) As t
>
> ON od.orderid = t.Item
>
>
>
>
>
> "Ben Watts" wrote in message
> news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>>I have some simple SQL code below.
>> SELECT DAT_V_MEDHIST.COMMENT01
>> FROM DAT_V_MEDHIST LEFT OUTER JOIN
>> STY_STUDY ON DAT_V_MEDHIST.STUDY = STY_STUDY.NAME
>> I need to take the comment filed and split it into 3 separate fields.
>> The
>> data in the comment field looks like this:
>> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
>> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
>> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
>> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
>> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
>> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>>
>> Basically, the first field will be the number which is followed by the
>> separator ¬. Then the second field follows that ¬ and goes until the
>> next ¬.
>> The third field starts after the second ¬ and and goes to the | and then
>> it
>> all starts over. I am not good enough in SQL to figure this one out. Is
>> their any genious out there that can help me out with this? Thanks in
>> advance!
>
>

Ben
Everything above /*usage example*/ you should run on SQL Server


"Ben Watts" wrote in message
news:%23tD6jCATKHA.4360atTK2MSFTNGP04dotphx.gbl...
> Thanks Uri,
>
> One more question, as I am not very good at SQL yet, do I need to fill
> anything in to the code you sent me or just put it in the dataset? Thanks
> again!
>
> "Uri Dimant" wrote in message
> news:O6USx$8SKHA.4704atTK2MSFTNGP02dotphx.gbl...
>> Ben
>> CREATE FUNCTION dbo.TsqlSplit
>>
>> (@List As varchar(8000))
>>
>> RETURNS @Items table (Item varchar(8000) Not Null)
>>
>> AS
>>
>> BEGIN
>>
>> DECLARE @Item As varchar(8000), @Pos As int
>>
>> WHILE DATALENGTH(@List)>0
>>
>> BEGIN
>>
>> SET @Pos=CHARINDEX(',',@List)
>>
>> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>>
>> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>>
>> IF @Item<>'' INSERT INTO @Items SELECT @Item
>>
>> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>>
>> END
>>
>> RETURN
>>
>> END
>>
>> GO
>>
>> /* Usage example */
>>
>> SELECT t1.*
>>
>> FROM TsqlSplit('10428,10429') AS t1
>>
>>
>> declare @inList varchar(50)
>>
>> set @inList='10428,10429'
>>
>> select od.* from [order details] od
>>
>> INNER JOIN
>>
>> (SELECT Item
>>
>> FROM dbo.TsqlSplit(@InList)) As t
>>
>> ON od.orderid = t.Item
>>
>>
>>
>>
>>
>> "Ben Watts" wrote in message
>> news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>>>I have some simple SQL code below.
>>> SELECT DAT_V_MEDHIST.COMMENT01
>>> FROM DAT_V_MEDHIST LEFT OUTER JOIN
>>> STY_STUDY ON DAT_V_MEDHIST.STUDY = STY_STUDY.NAME
>>> I need to take the comment filed and split it into 3 separate fields.
>>> The
>>> data in the comment field looks like this:
>>> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
>>> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
>>> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
>>> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
>>> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
>>> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>>>
>>> Basically, the first field will be the number which is followed by the
>>> separator ¬. Then the second field follows that ¬ and goes until the
>>> next ¬.
>>> The third field starts after the second ¬ and and goes to the | and then
>>> it
>>> all starts over. I am not good enough in SQL to figure this one out.
>>> Is
>>> their any genious out there that can help me out with this? Thanks in
>>> advance!
>>
>>
>
>

I meant more along the lines of the @items. Do I replace those with fields
or table names. Sorry to be so dingy!
"Uri Dimant" wrote in message
news:%23g5rGeATKHA.4704atTK2MSFTNGP02dotphx.gbl...
> Ben
> Everything above /*usage example*/ you should run on SQL Server
>
>
> "Ben Watts" wrote in message
> news:%23tD6jCATKHA.4360atTK2MSFTNGP04dotphx.gbl...
>> Thanks Uri,
>>
>> One more question, as I am not very good at SQL yet, do I need to fill
>> anything in to the code you sent me or just put it in the dataset?
>> Thanks again!
>>
>> "Uri Dimant" wrote in message
>> news:O6USx$8SKHA.4704atTK2MSFTNGP02dotphx.gbl...
>>> Ben
>>> CREATE FUNCTION dbo.TsqlSplit
>>>
>>> (@List As varchar(8000))
>>>
>>> RETURNS @Items table (Item varchar(8000) Not Null)
>>>
>>> AS
>>>
>>> BEGIN
>>>
>>> DECLARE @Item As varchar(8000), @Pos As int
>>>
>>> WHILE DATALENGTH(@List)>0
>>>
>>> BEGIN
>>>
>>> SET @Pos=CHARINDEX(',',@List)
>>>
>>> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>>>
>>> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>>>
>>> IF @Item<>'' INSERT INTO @Items SELECT @Item
>>>
>>> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>>>
>>> END
>>>
>>> RETURN
>>>
>>> END
>>>
>>> GO
>>>
>>> /* Usage example */
>>>
>>> SELECT t1.*
>>>
>>> FROM TsqlSplit('10428,10429') AS t1
>>>
>>>
>>> declare @inList varchar(50)
>>>
>>> set @inList='10428,10429'
>>>
>>> select od.* from [order details] od
>>>
>>> INNER JOIN
>>>
>>> (SELECT Item
>>>
>>> FROM dbo.TsqlSplit(@InList)) As t
>>>
>>> ON od.orderid = t.Item
>>>
>>>
>>>
>>>
>>>
>>> "Ben Watts" wrote in message
>>> news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>>>>I have some simple SQL code below.
>>>> SELECT DAT_V_MEDHIST.COMMENT01
>>>> FROM DAT_V_MEDHIST LEFT OUTER JOIN
>>>> STY_STUDY ON DAT_V_MEDHIST.STUDY = STY_STUDY.NAME
>>>> I need to take the comment filed and split it into 3 separate fields.
>>>> The
>>>> data in the comment field looks like this:
>>>> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
>>>> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
>>>> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
>>>> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
>>>> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
>>>> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>>>>
>>>> Basically, the first field will be the number which is followed by the
>>>> separator ¬. Then the second field follows that ¬ and goes until the
>>>> next ¬.
>>>> The third field starts after the second ¬ and and goes to the | and
>>>> then it
>>>> all starts over. I am not good enough in SQL to figure this one out.
>>>> Is
>>>> their any genious out there that can help me out with this? Thanks in
>>>> advance!
>>>
>>>
>>
>>
>
>

I'm sorry I guess I am not sure when and where to use this? Because my
problem is occurring inside my dataset of my report.
"Ben Watts" wrote in message
news:%239gRokATKHA.4324atTK2MSFTNGP05dotphx.gbl...
>I meant more along the lines of the @items. Do I replace those with fields
>or table names. Sorry to be so dingy!
> "Uri Dimant" wrote in message
> news:%23g5rGeATKHA.4704atTK2MSFTNGP02dotphx.gbl...
>> Ben
>> Everything above /*usage example*/ you should run on SQL Server
>>
>>
>> "Ben Watts" wrote in message
>> news:%23tD6jCATKHA.4360atTK2MSFTNGP04dotphx.gbl...
>>> Thanks Uri,
>>>
>>> One more question, as I am not very good at SQL yet, do I need to fill
>>> anything in to the code you sent me or just put it in the dataset?
>>> Thanks again!
>>>
>>> "Uri Dimant" wrote in message
>>> news:O6USx$8SKHA.4704atTK2MSFTNGP02dotphx.gbl...
>>>> Ben
>>>> CREATE FUNCTION dbo.TsqlSplit
>>>>
>>>> (@List As varchar(8000))
>>>>
>>>> RETURNS @Items table (Item varchar(8000) Not Null)
>>>>
>>>> AS
>>>>
>>>> BEGIN
>>>>
>>>> DECLARE @Item As varchar(8000), @Pos As int
>>>>
>>>> WHILE DATALENGTH(@List)>0
>>>>
>>>> BEGIN
>>>>
>>>> SET @Pos=CHARINDEX(',',@List)
>>>>
>>>> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>>>>
>>>> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>>>>
>>>> IF @Item<>'' INSERT INTO @Items SELECT @Item
>>>>
>>>> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>>>>
>>>> END
>>>>
>>>> RETURN
>>>>
>>>> END
>>>>
>>>> GO
>>>>
>>>> /* Usage example */
>>>>
>>>> SELECT t1.*
>>>>
>>>> FROM TsqlSplit('10428,10429') AS t1
>>>>
>>>>
>>>> declare @inList varchar(50)
>>>>
>>>> set @inList='10428,10429'
>>>>
>>>> select od.* from [order details] od
>>>>
>>>> INNER JOIN
>>>>
>>>> (SELECT Item
>>>>
>>>> FROM dbo.TsqlSplit(@InList)) As t
>>>>
>>>> ON od.orderid = t.Item
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Ben Watts" wrote in message
>>>> news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>>>>>I have some simple SQL code below.
>>>>> SELECT DAT_V_MEDHIST.COMMENT01
>>>>> FROM DAT_V_MEDHIST LEFT OUTER JOIN
>>>>> STY_STUDY ON DAT_V_MEDHIST.STUDY = STY_STUDY.NAME
>>>>> I need to take the comment filed and split it into 3 separate fields.
>>>>> The
>>>>> data in the comment field looks like this:
>>>>> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
>>>>> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
>>>>> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
>>>>> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
>>>>> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
>>>>> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>>>>>
>>>>> Basically, the first field will be the number which is followed by the
>>>>> separator ¬. Then the second field follows that ¬ and goes until the
>>>>> next ¬.
>>>>> The third field starts after the second ¬ and and goes to the | and
>>>>> then it
>>>>> all starts over. I am not good enough in SQL to figure this one out.
>>>>> Is
>>>>> their any genious out there that can help me out with this? Thanks in
>>>>> advance!
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Ben
You buld the dataset based on this SELECT statement (which used UDF)



"Ben Watts" wrote in message
news:%23gEGZEBTKHA.4692atTK2MSFTNGP06dotphx.gbl...
> I'm sorry I guess I am not sure when and where to use this? Because my
> problem is occurring inside my dataset of my report.
> "Ben Watts" wrote in message
> news:%239gRokATKHA.4324atTK2MSFTNGP05dotphx.gbl...
>>I meant more along the lines of the @items. Do I replace those with
>>fields or table names. Sorry to be so dingy!
>> "Uri Dimant" wrote in message
>> news:%23g5rGeATKHA.4704atTK2MSFTNGP02dotphx.gbl...
>>> Ben
>>> Everything above /*usage example*/ you should run on SQL Server
>>>
>>>
>>> "Ben Watts" wrote in message
>>> news:%23tD6jCATKHA.4360atTK2MSFTNGP04dotphx.gbl...
>>>> Thanks Uri,
>>>>
>>>> One more question, as I am not very good at SQL yet, do I need to fill
>>>> anything in to the code you sent me or just put it in the dataset?
>>>> Thanks again!
>>>>
>>>> "Uri Dimant" wrote in message
>>>> news:O6USx$8SKHA.4704atTK2MSFTNGP02dotphx.gbl...
>>>>> Ben
>>>>> CREATE FUNCTION dbo.TsqlSplit
>>>>>
>>>>> (@List As varchar(8000))
>>>>>
>>>>> RETURNS @Items table (Item varchar(8000) Not Null)
>>>>>
>>>>> AS
>>>>>
>>>>> BEGIN
>>>>>
>>>>> DECLARE @Item As varchar(8000), @Pos As int
>>>>>
>>>>> WHILE DATALENGTH(@List)>0
>>>>>
>>>>> BEGIN
>>>>>
>>>>> SET @Pos=CHARINDEX(',',@List)
>>>>>
>>>>> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>>>>>
>>>>> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>>>>>
>>>>> IF @Item<>'' INSERT INTO @Items SELECT @Item
>>>>>
>>>>> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>>>>>
>>>>> END
>>>>>
>>>>> RETURN
>>>>>
>>>>> END
>>>>>
>>>>> GO
>>>>>
>>>>> /* Usage example */
>>>>>
>>>>> SELECT t1.*
>>>>>
>>>>> FROM TsqlSplit('10428,10429') AS t1
>>>>>
>>>>>
>>>>> declare @inList varchar(50)
>>>>>
>>>>> set @inList='10428,10429'
>>>>>
>>>>> select od.* from [order details] od
>>>>>
>>>>> INNER JOIN
>>>>>
>>>>> (SELECT Item
>>>>>
>>>>> FROM dbo.TsqlSplit(@InList)) As t
>>>>>
>>>>> ON od.orderid = t.Item
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "Ben Watts" wrote in message
>>>>> news:F8F6615B-0CC8-43EC-87DC-6792DC5658A9atmicrosoftdotcom...
>>>>>>I have some simple SQL code below.
>>>>>> SELECT DAT_V_MEDHIST.COMMENT01
>>>>>> FROM DAT_V_MEDHIST LEFT OUTER JOIN
>>>>>> STY_STUDY ON DAT_V_MEDHIST.STUDY =
>>>>>> STY_STUDY.NAME
>>>>>> I need to take the comment filed and split it into 3 separate fields.
>>>>>> The
>>>>>> data in the comment field looks like this:
>>>>>> 3¬UND-UNM-UNY ONGOING¬MALALIGNMENT OF TEETH|4¬UND-UNM-UNY
>>>>>> ONGOING¬MILD PECTUS DEFORMITY|9¬UND-UNM-UNY ONGOING¬MILD ACNE ON
>>>>>> BACK, SCARS-OLD BURNS ON FOREARMS, OLD LACERATION OVER RIGHT EYEBROW,
>>>>>> PIERCINGS-EAR LOBES AND 2 ON LOWER LIP, TATTOOS RIGHT
>>>>>> ANTERIOR SUPERIOR ILIAC SPINE AREA AND
>>>>>> RIGHT ARM|7¬01-OCT-2007 SAME¬CHLAMYDIA--TREATMENT WAS ZITHROMAX
>>>>>>
>>>>>> Basically, the first field will be the number which is followed by
>>>>>> the
>>>>>> separator ¬. Then the second field follows that ¬ and goes until the
>>>>>> next ¬.
>>>>>> The third field starts after the second ¬ and and goes to the | and
>>>>>> then it
>>>>>> all starts over. I am not good enough in SQL to figure this one out.
>>>>>> Is
>>>>>> their any genious out there that can help me out with this? Thanks
>>>>>> in
>>>>>> advance!
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>