Sql String Output

I am creating a sql loop that is preparing an output string.  For some reason, I'm having difficulty with my setting @output_string variable.  The value should be the last loop value + the current loop value.  What i'm puroducing is the last loop value twice.

I know this is a simple oversight.  Thanks for the assistance.

David
-- incoming parameter
declare @incoming_string varchar(255)
set @incoming_string = '4-8'

-- counter
declare @counter int
set @counter = 1


declare @output_string varchar(255)
set @output_string = ''

-- This loops through the string one more count than there are '-'
While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
	Begin

		-- Step 1: Set the string
		declare @string varchar(255)
		-- Take the incoming value on the first loop
		If @counter = 1 BEGIN
			set @string = @incoming_string
		END 

		-- Take the value created in this query for successive loops
		If @counter > 1 Begin
			set @string = @string
		End

		-- Step 2: Set the start point
		declare @start int
			set @start = 1

		-- Step 3: Find the position of the first "-" in the string less 1
		declare @break int
		if dbo.ufn_countChar(@string,'-') = 0 Begin
			set @break = len(@string) End

		-- This would cover the last loop
		if dbo.ufn_countchar(@string,'-') > 0 Begin
			set @break = charindex('-',@string,1) - 1 End




		-- Step 5: Set the new String
		set @string = Right(@string,@break)

		set @output_string = (
			select	
				vchr_Segment_Descr
			from
				dbo.tbl_Control_Accounts_Detail
			Where
				int_CAD_Record_Number = left(@string,@break))

	set @output_string = @output_string + '-' + @output_string

	set @counter = @counter + 1


End


print @output_string

Open in new window

gdspeareAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
check this.

this piece of code will give you the expected result.

select rtrim(substring(isnull((select '-'+vchr_segment_descr from #t t1 for xml path('')),' '),2,2000))

alter FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END
go
 
create table #t (vchr_segment_descr varchar(255))

create table #tbl_Control_Accounts_Detail (
int_CAD_Record_Number int
,vchr_Segment_Descr varchar(255)
)

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('1','West')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values('2','North')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('3','East')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('4','South')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('5','S. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('6','N. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('7','New Braunfels')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('8','San Antonio')


-- incoming parameter
declare @incoming_string varchar(255)
set @incoming_string = '4-8-7-4'

-- counter
declare @counter int
set @counter = 1

While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
Begin

-- Step 1: Set the string
declare @string varchar(255)
If @counter = 1 BEGIN
set @string = @incoming_string
END 

If @counter > 1 Begin
set @string = @string
End

-- Step 2: Set the start point
declare @start int
set @start = 1

-- Step 3: Find the position of the first "-" in the string less 1 position
declare @break int
if dbo.ufn_countChar(@string,'-') = 0 Begin
set @break = len(@string) End

if dbo.ufn_countchar(@string,'-') > 0 Begin
set @break = charindex('-',@string,1) - 1 End

-- Step 4: Parse out the string
Insert into #t
select  
        vchr_Segment_Descr
from
        #tbl_Control_Accounts_Detail
Where
        int_CAD_Record_Number = left(@string,@break)





-- Step 5: Set the new String
set @string = substring(@string,@break+2,len(@string))


set @counter = @counter + 1

End
print @string
select rtrim(substring(isnull((select '-'+vchr_segment_descr from #t t1 for xml path('')),' '),2,2000))


drop table #t
drop table #tbl_Control_Accounts_Detail

Open in new window

0
 
Kamaraj SubramanianApplication Support AnalystCommented:
try this
-- incoming parameter 
declare @incoming_string varchar(255) 
set @incoming_string = '4-8' 
 
-- counter 
declare @counter int 
set @counter = 1 
 
 
declare @output_string varchar(255),
@temp_string varchar(255) 
set @output_string = '' 
set @temp_string =''
 
-- This loops through the string one more count than there are '-' 
While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2 
        Begin 
 
                -- Step 1: Set the string 
                declare @string varchar(255) 
                -- Take the incoming value on the first loop 
                If @counter = 1 BEGIN 
                        set @string = @incoming_string 
                END  
 
                -- Take the value created in this query for successive loops 
                If @counter > 1 Begin 
                        set @string = @string 
                End 
 
                -- Step 2: Set the start point 
                declare @start int 
                        set @start = 1 
 
                -- Step 3: Find the position of the first "-" in the string less 1 
                declare @break int 
                if dbo.ufn_countChar(@string,'-') = 0 Begin 
                        set @break = len(@string) End 
 
                -- This would cover the last loop 
                if dbo.ufn_countchar(@string,'-') > 0 Begin 
                        set @break = charindex('-',@string,1) - 1 End 
 
 
 
 
                -- Step 5: Set the new String 
                set @string = Right(@string,@break) 
 
                set @output_string = ( 
                        select   
                                vchr_Segment_Descr 
                        from 
                                dbo.tbl_Control_Accounts_Detail 
                        Where 
                                int_CAD_Record_Number = left(@string,@break)) 
		
 
        set @output_string = @output_string + '-' + @temp_string 
		set @temp_string = @output_string
 
        set @counter = @counter + 1 
 
 
End 
 
 
print @output_string

Open in new window

0
 
igni7eCommented:
Are you trying to do this?
If not, I'm not sure what the two values you want are.

 set @output_string = @output_string + '-' + (
                        select  
                                vchr_Segment_Descr
                        from
                                dbo.tbl_Control_Accounts_Detail
                        Where
                                int_CAD_Record_Number = left(@string,@break))

Remove:
        set @output_string = @output_string + '-' + @output_string
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Kamaraj SubramanianApplication Support AnalystCommented:
I think the above will not work. Please wait, i am re-checking this.
0
 
sameer2010Commented:
Hi,

I think, there are too much of redundant code here. Can you post input/output combinations for few cases and we can provide you with better performing code?
0
 
gdspeareAuthor Commented:
The combination used in the code 4-8 should be producing the result "South-San Antonio".

Any two words could be substitued here.  In addition, the comination might not be limited to only 2 values.  The user can have as many as they like.
0
 
gdspeareAuthor Commented:
itkamaraj: - your code produces "San Antonio-San Antonio-"  Rather than the "South-San Antonio" value i expect.
0
 
gdspeareAuthor Commented:
Here is some sample code that might help understand my issue.

This sample works just fine as you will notice. Each pass inserts the data into the temp table as expectsd.  So I'm confident my code works...i just need it as one string.

David
ALTER FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END

 
create table #t (vchr_segment_descr varchar(255))

create table #tbl_Control_Accounts_Detail (
int_CAD_Record_Number int
,vchr_Segment_Descr varchar(255)
)

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('1','West')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values('2','North')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('3','East')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('4','South')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('5','S. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('6','N. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('7','New Braunfels')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('8','San Antonio')


-- incoming parameter
declare @incoming_string varchar(255)
set @incoming_string = '4-8-7-4'

-- counter
declare @counter int
set @counter = 1

While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
Begin

-- Step 1: Set the string
declare @string varchar(255)
If @counter = 1 BEGIN
set @string = @incoming_string
END 

If @counter > 1 Begin
set @string = @string
End

-- Step 2: Set the start point
declare @start int
set @start = 1

-- Step 3: Find the position of the first "-" in the string less 1 position
declare @break int
if dbo.ufn_countChar(@string,'-') = 0 Begin
set @break = len(@string) End

if dbo.ufn_countchar(@string,'-') > 0 Begin
set @break = charindex('-',@string,1) - 1 End

-- Step 4: Parse out the string
Insert into #t
select	
	vchr_Segment_Descr
from
	#tbl_Control_Accounts_Detail
Where
	int_CAD_Record_Number = left(@string,@break)





-- Step 5: Set the new String
set @string = substring(@string,@break+2,len(@string))


set @counter = @counter + 1

End

print @string
select * from #t
drop table #t
drop table #tbl_Control_Accounts_Detail

Open in new window

0
 
SharathData EngineerCommented:
check this
alter FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END
go
 
create table #t (vchr_segment_descr varchar(255))

create table #tbl_Control_Accounts_Detail (
int_CAD_Record_Number int
,vchr_Segment_Descr varchar(255)
)

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('1','West')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values('2','North')

Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('3','East')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('4','South')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('5','S. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('6','N. Austin')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('7','New Braunfels')
Insert into #tbl_Control_Accounts_Detail (int_CAD_Record_Number,vchr_Segment_Descr)
Values ('8','San Antonio')


-- incoming parameter
declare @incoming_string varchar(255)
set @incoming_string = '4-8-7-4'

-- counter
declare @counter int
set @counter = 1

While @counter < dbo.ufn_CountChar(@incoming_string,'-') + 2
Begin

-- Step 1: Set the string
declare @string varchar(255)
If @counter = 1 BEGIN
set @string = @incoming_string
END 

If @counter > 1 Begin
set @string = @string
End

-- Step 2: Set the start point
declare @start int
set @start = 1

-- Step 3: Find the position of the first "-" in the string less 1 position
declare @break int
if dbo.ufn_countChar(@string,'-') = 0 Begin
set @break = len(@string) End

if dbo.ufn_countchar(@string,'-') > 0 Begin
set @break = charindex('-',@string,1) - 1 End

-- Step 4: Parse out the string
Insert into #t
select  
        vchr_Segment_Descr
from
        #tbl_Control_Accounts_Detail
Where
        int_CAD_Record_Number = left(@string,@break)





-- Step 5: Set the new String
set @string = substring(@string,@break+2,len(@string))


set @counter = @counter + 1

End

print @string
select max(case when rn = 1 then vchr_segment_descr end)+' '+max(case when rn = 2 then vchr_segment_descr end)
  from (select *,row_number() over (order by (select 1)) rn 
          from #t) as t1 where rn in (1,2)
drop table #t
drop table #tbl_Control_Accounts_Detail

Open in new window

0
 
SharathData EngineerCommented:
With your code, the result is

South
San Antonio
New Braunfels
South

I picked the first two values (South and San Antonio) and concatenated both the strings.
Let me know if you are looking for something else.
0
 
gdspeareAuthor Commented:
I am looking for one string output.  In this case I would be looking for

South-San Antonio-New Braunfels-South

as one record.

My query is producing 4 (in this case) distinct records.
0
 
gdspeareAuthor Commented:
I didnt' see your first post, Sharath.  I need ALL values to be returned and concatenated.  

Originally i was trying to set the looped value to a string and then with each pass update the string value to be the old value + the new value....

...any ideas there?
0
 
gdspeareAuthor Commented:
Perfect...

I'm going to open up a brand new question.  THat is going to ask how to turn this into a function.

I hope you will get those 500 points also.

Thank you.
0
 
gdspeareAuthor Commented:
Thank you.
0
 
SharathData EngineerCommented:
you are welcome.

you can provide the link to your new question here. I will look into that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.