Link to home
Start Free TrialLog in
Avatar of gdspeare
gdspeare

asked on

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

Avatar of Kamaraj Subramanian
Kamaraj Subramanian
Flag of Singapore image
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

Avatar of igni7e
igni7e
Flag of Ireland image
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
Avatar of Kamaraj Subramanian
Kamaraj Subramanian
Flag of Singapore image
I think the above will not work. Please wait, i am re-checking this.
Avatar of sameer2010
sameer2010
Flag of India image
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?
Avatar of gdspeare
gdspeare

ASKER

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.
Avatar of gdspeare
gdspeare

ASKER

itkamaraj: - your code produces "San Antonio-San Antonio-"  Rather than the "South-San Antonio" value i expect.
Avatar of gdspeare
gdspeare

ASKER

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

Avatar of Sharath S
Sharath S
Flag of United States of America image
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

Avatar of Sharath S
Sharath S
Flag of United States of America image
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.
Avatar of gdspeare
gdspeare

ASKER

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.
Avatar of gdspeare
gdspeare

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gdspeare
gdspeare

ASKER

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.
Avatar of gdspeare
gdspeare

ASKER

Thank you.
Avatar of Sharath S
Sharath S
Flag of United States of America image
you are welcome.

you can provide the link to your new question here. I will look into that.