I am a newbie to active-x and am trying to use the code at
http://www.sqldts.com/default.aspx?266 but to do something
a little different. I have a last_name column in the
source that I need to evaluate the number of spaces and
perform an action based on it.

If one space, take the data before the first space and put
it in the first_name destination, the data after the space
in the last_name destination.

If two spaces, take data before first space, put in
first_name destination, data between the spaces to the
middle_name destination and data after the second space
into the last_name destination.

If more than two spaces, take data before first space, put
in first_name and all the rest in last_name destination.

Forgive my activex ignorance, probably something simple.

Ray Higdon

Hi Ray,
Try this:

Create FUNCTION [dbo].[CountChar] (@String varchar(8000), @char varchar(1))
RETURNS int AS
BEGIN
Declare @count as Int
Declare @posStr as Int
Set @count=0
Set @posStr=CharIndex(@char,@String)
While @posStr > 0
Begin
Set @count=@count+1
Set @string=Stuff(@string,@posStr,CharIndex(@Char,@str ing)-@posStr+1,'')
Set @posStr=CharIndex(@char,@string)
End
Return @count
END

Select dbo.CountChar('Meneses Junior',' ')

HTH,


----- Ray Higdon wrote: -----

I am a newbie to active-x and am trying to use the code at
http://www.sqldts.com/default.aspx?266 but to do something
a little different. I have a last_name column in the
source that I need to evaluate the number of spaces and
perform an action based on it.

If one space, take the data before the first space and put
it in the first_name destination, the data after the space
in the last_name destination.

If two spaces, take data before first space, put in
first_name destination, data between the spaces to the
middle_name destination and data after the second space
into the last_name destination.

If more than two spaces, take data before first space, put
in first_name and all the rest in last_name destination.

Forgive my activex ignorance, probably something simple.

Ray Higdon

A quick way to do this would look like this

Imagine "str" is a source column


dim str
dim diff
dim arr
dim fName
dim mName
dim LName

str = "Ray Higdon"

diff = len(str) - Len(Replace( str ," " ,"" ,1 ))

msgbox "There were " & Cstr(diff) & " spaces"

arr = split(trim(str)," ")



msgbox "The array contains " & Cstr(UBOUND(arr)) & " elements"

select case UBOUND(arr)

case 1

msgbox "FirstName = " & arr(0)
msgbox "SurName = " & arr(1)


case 2

msgbox "FirstName = " & arr(0)
msgbox "Initial = " & arr(1)
msgbox "Surname = " & arr(2)


end select


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ray Higdon" <anonymous (AT) discussions (DOT) microsoft.com> wrote

This works great for single strings, how can I modify it
for data out of a column?

Specifically, something like

update table
set num_spaces = yourfunction(mycolumn,' ')

Appreciate any help.

Nevermind, I used a silly like '% % % %' statement to find
more than 3 columns and used it, thanks guys

Thanks Allan, it turned out there was way more scrubbing needed than I
originally anticipated. I ended up making a separate SQL task and scrubbing
the data.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote