One for the SQL Gurus: Split a delimited field into rows

Hi.

I'm trying to write an SQL Query that will take a delimited field and return each item as a row.

Example

Take the AuthorizedRoles and TabID fields from the Tabs table

AuthorizedRoles TabID
0;11;__________1
0; 15 ;17;______6
-2;____________7

I would like to return a unique record for each Authorized Role

AuthorizedRole TabID
0____________1
11___________1
0____________6
15___________6
17___________6
-2___________7

Any ideas?

Cheers
Davecheck if thisarticle helps

hth|||Thanks Dinakar. I was hoping to do this entirely within the SELECT statement but realise that's probably impossible without built in SQL commands like split etc..

This example looks like a good place to start. All other examples I've seen require temp tables and Lookup tables.

Cheers
Dave

One Date field with different dates

I want to search on a date field with user input. How can I make it to where
it will give user a startdate and an enddate box? Remember I have only one
date field.
SELECT COURSE_NBR, ATTEND, COMPL_DATE
FROM EH_MEMINFO_1
WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
(COMPL_DATE LIKE @.COMPL_DATE)
Thanks fo ryour help.Use this:
SELECT COURSE_NBR, ATTEND, COMPL_DATE
FROM EH_MEMINFO_1
WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
(COMPL_DATE BETWEEN @.StartDate AND @.EndDate)
"Shan" wrote:
> I want to search on a date field with user input. How can I make it to where
> it will give user a startdate and an enddate box? Remember I have only one
> date field.
> SELECT COURSE_NBR, ATTEND, COMPL_DATE
> FROM EH_MEMINFO_1
> WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> (COMPL_DATE LIKE @.COMPL_DATE)
> Thanks fo ryour help.|||O Thank you soo much...that solved lots of problems.
Great Daw!
"daw" wrote:
> Use this:
> SELECT COURSE_NBR, ATTEND, COMPL_DATE
> FROM EH_MEMINFO_1
> WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> (COMPL_DATE BETWEEN @.StartDate AND @.EndDate)
>
> "Shan" wrote:
> > I want to search on a date field with user input. How can I make it to where
> > it will give user a startdate and an enddate box? Remember I have only one
> > date field.
> >
> > SELECT COURSE_NBR, ATTEND, COMPL_DATE
> > FROM EH_MEMINFO_1
> > WHERE (COURSE_NBR LIKE @.COURSE_NBR) or
> > (COMPL_DATE LIKE @.COMPL_DATE)
> >
> > Thanks fo ryour help.

OLEDB Access PB

Hi

I definite a source OLEDB MS Access and my fields of the type “Text” are seen in type of field DT_WSTR (Unicode) instead of DT_STR.

I do not include/understand why? and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution?

thank for your solution

You can use a Derived Column transform to convert between Unicode and Ansi. Use the operators under the Type Casts section.

Code Snippet

(DT_STR, 50, 1252) [Your_Column]

|||

Hi plab,

You may even use a DataConversion transformation to change type DT_WSTR to DT_STR with a required length.

Thanks

Subhash Subramanyam

OlE Db Command

Hi,

Iam trying to generate the increment value for a column by taking Maximum value of that field by using OLE DB Command Transformation.

This is the query iam using for that :
select max(ApplicationId)+1 as APPLICATIONID from Source..Applications

Iam finding difficulty in getting the 'APPLICATIONID' as the out put from OLEDB Transformation.OLE DB is not going to work with out any parameters ?can you please throw some light on this .First time iam using this transformation.

Another alternate solution is generating through script task (Thanks Jamie Thomson for his article).But i dont know how i can modify this to get the Max value instead of declaring some constant value for increment

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Int32
Public Sub New()
Counter = 1006
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Counter += 1
Row.APPLICATIONTASKID = Counter

End Sub

End Class
Please me help me in finding one solution from the above two

Thanks
Niru
No replies ..

Am I dint explained properly or nobody are aware of this?

Thanks
Niru
|||

If I understand your question correctly, you're asking to generate a surrogate key id (1,2,3,..) which key id is started from last key id + 1 from your source table. If yes, you can try this:

On control flow, create an Execute SQL task with your select statement:

select isnull(max(ApplicationId),1) as APPLICATIONID from Source..Applications

Set Result Set property = Single Row

Create a variable, on the Result Set assign your variable.

On data flow, create a column, ex. ApplicationKeyID set to null. Follow by script component task; in Input Columns, select the ApplicationKeyID and Usage type = ReadWrite. On Script, type in the variable name for ReadOnlyVariables property. And your script will look something like this:

Public Class ScriptMain

Inherits UserComponent

Dim Counter As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.ApplicationKeyID = Variables.VariableName+ Counter

Counter = Counter + 1

End Sub

End Class

Hope this does not cause any more confusion for you...