Problem with comma's in field Help needed 1

[Chuck712]

I am sure that I am missing something since I have been on this same problem for a few days,but...
I have a column in a table that I need to "de-concatenate"
I thought this was working in the past, but now when I view the results and compare them to the field, I am missing the last phrase. The data looks like this:

Heart & vascular health,Health screenings,Cancer detection & treatment,Bone & joint care,Men's health

Here is the code:

ALTER  FUNCTION dbo.client_health_topics_ind(@ind_cst_ext_key varchar(38))
RETURNS @HealthTopic Table(HealthTopic VarChar(600),ind_cst_key_ext varchar(38))
AS
BEGIN
    DECLARE @HealthDescripB TABLE(
     HealthDescrip VarChar(600),
         ind_cst_key_ext varchar (38)
         )
        Insert Into @HealthTopic(HealthTopic,ind_cst_key_ext) 
            Select LTrim(Rtrim(ind_health_topic_of_interest_ext)),
                       ind_cst_key_ext
    From co_individual_ext
    WHERE ind_health_topic_of_interest_ext IS NOT NULL
    While Exists(Select * From @HealthTopic Where CharIndex(',', HealthTopic) > 0)
          Begin
                Insert Into @HealthDescripB(HealthDescrip,ind_cst_key_ext)               
    Select Left(HealthTopic, CharIndex(',', HealthTopic)-1),
                ind_cst_key_ext
    From   @HealthTopic
    Where  CharIndex(',', HealthTopic) > 0
    Update @HealthTopic
    Set    HealthTopic = LTrim(RTrim(Right(HealthTopic, Len(HealthTopic)-CharIndex(',', HealthTopic))))
END 

Insert Into @HealthDescripB(HealthDescrip,ind_cst_key_ext) 
Select Replace(HealthTopic, ',', ''),ind_cst_key_ext
From @HealthTopic
RETURN

END

Here are the results:

Heart & vascular health	2A24598A-2B06-482C-824F-00085DF86F97
Health screenings	2A24598A-2B06-482C-824F-00085DF86F97
Cancer detection & treatment	2A24598A-2B06-482C-824F-00085DF86F97
Bone & joint care	2A24598A-2B06-482C-824F-00085DF86F97

Men's Health is missing from the results.
I can't seem to see where I can change that selection set to get me all of the field.
I started a new thread since this is not related to the earlier problem that I have been having with this.
Any help would be appreciated

 

[SgtJarrow]

I think you problem lies in you While Exists loop....You are changing the HealthTopic variable as you loop and the line:
Set HealthTopic = LTrim(RTrim(Right(HealthTopic, Len(HealthTopic)-CharIndex(',', HealthTopic))))
is setting HealthTopic = '' on the last loop because the comma is at the first position

I think you need to include an IF statement which encapsulates the Set HelathTopic line and only execute if the comma is not in the first position.

But I am not a SQL guru as my main area is VB. Hopefully someone with more SQL experience can confirm my thought.

Good luck!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Chuck712]

That could be. I have also discovered that if there is only one entry in the field there will be no comma in the string so that entry would be excluded. Hopefully someone can get me a solution.

 

[gmmastros]

I think you have your tables switched around. It's hard to explain, so... I'd like you to try this query to see if it works for you. If you post back that it works, and want to know why I changed the things that I did, then I will gladly explain it for you.

ALTER  FUNCTION dbo.client_health_topics_ind(@ind_cst_ext_key varchar(38))
RETURNS @HealthTopic Table(HealthTopic VarChar(600),ind_cst_key_ext varchar(38))
AS
BEGIN

    DECLARE @HealthDescripB 
	TABLE	(
			HealthDescrip VarChar(600),
			ind_cst_key_ext varchar (38)
			)

	Insert 
	Into 	@HealthDescripB(HealthDescrip,ind_cst_key_ext) 
    Select 	LTrim(Rtrim(ind_health_topic_of_interest_ext)),
            ind_cst_key_ext
    From 	@Temp
    WHERE 	ind_health_topic_of_interest_ext IS NOT NULL

    While Exists(Select * From @HealthDescripB Where CharIndex(',', HealthDescrip) > 0)
          Begin
                Insert Into @HealthTopic(HealthTopic,ind_cst_key_ext)               
    Select Left(HealthDescrip, CharIndex(',', HealthDescrip)-1),
                ind_cst_key_ext
    From   @HealthDescripB
    Where  CharIndex(',', HealthDescrip) > 0
    Update @HealthDescripB
    Set    HealthDescrip = LTrim(RTrim(Right(HealthDescrip, Len(HealthDescrip)-CharIndex(',', HealthDescrip))))
END 

Insert Into @HealthTopic(HealthTopic,ind_cst_key_ext) 
Select Replace(HealthDescrip, ',', ''),ind_cst_key_ext
From @HealthDescripB

RETURN

END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chuck712]

Error on not declaring @Temp

 

[gmmastros]

Sorry. That was a remnant of my testing.

Replace @Temp with [!]co_individual_ex[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chuck712]

Got it. Thanks. I need to lock the keys, since this returned all rows, but on first glance, it looks right. And please tell me where I went wrong. You might recognize some of this code from a post in your archive.

 

[Chuck712]

Still came back missing the last entry Men's Health. I think it's because there is no comma after that. The field looks like this:
Heart & vascular health, Health screenings, Cancer detection & treatment, Bone & joint care, Men's health

 

[Chuck712]

I have to leave for the day, so I will pick this up tomorrow. Thanks again for all of your help.

 

[gmmastros]

Let me explain this with an example.

Suppose you have a description table that looks like this...

[tt]
ID Data
-- -------------
1 A,B,C,D
2 X,Y
[/tt]

The first thing we need to do is to create an output table (In your function, this is created as the RETURN table).

Each time through the loop, we remove the first part of the string and add it to the output table.

[!]Before the loop[/!]
[tt]
[blue]Description Table[/blue]
ID Data
-- -------------
1 A,B,C,D
2 X,Y


[blue]Topic Table[/blue]
ID Data
-- ------
[/tt]

[!]After the first loop, we have....[/!]

[tt]
[blue]Description Table[/blue]
ID Data
-- -------------
1 B,C,D
2 Y


[blue]Topic Table[/blue]
ID Data
-- ------
1 A
2 X
[/tt]

[!]After the 2nd time through the loop, we have...[/!]

[tt]
[blue]Description Table[/blue]
ID Data
-- -------------
1 C,D
2 Y


[blue]Topic Table[/blue]
ID Data
-- ------
1 A
2 X
1 B
[/tt]

Remember that we are only removing the first bit of data if there is a comma in the string. Since ID = 2 doesn't have a comma, nothing is removed.

[!]Loop again....[/!]

[tt]
[blue]Description Table[/blue]
ID Data
-- -------------
1 D
2 Y


[blue]Topic Table[/blue]
ID Data
-- ------
1 A
2 X
1 B
1 C
[/tt]

At this point, we simply need to add the remaining data to the output table. The problem you had is that you had your tables reversed. Initially, you added all your data to the output table (@HealthTopic) and slowly removed the data from it and inserted it in to the description table. Instead, you needed to add the data to the description table and slowly add it to the output table.

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[traingamer]

While Exists(Select * From @HealthTopic Where CharIndex(',', HealthTopic) > 0)

will never find anything after the last comma.

See Create User Defined Function or various other ways of writing a 'split' function.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gmmastros]

traingamer,

That is what the last insert (just before the RETURN) is doing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[traingamer]

Sorry. Pay no attention to the man behind the curtain.

 

[Chuck712]

Thanks. Someday I will repay someone for all the help this has been. I suspected that I had reversed the tables, but I wasn't sure that was the problem. Your explination was perfect and thank you again for your help. We had worked on one of these before you might recall. The correct results did return.

 

[Chuck712]

Does any one have some syntax tips to get this output into a view? Should I create the view inside the function?

 

[SQLBill]

I don't follow you.....why make a view? You use the function to create a table.

What exactly are you trying to do?


-SQLBill

Posting advice: FAQ481-4875

 

[Chuck712]

Thanks. The application that interfaces with users will only accept a view. I was able to create it and it works I appreciate everyones help. Thanks again.