[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql String Output

Posted on 2009-12-31
15
Medium Priority
?
382 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:gdspeare
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 26153858
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
 
LVL 4

Expert Comment

by:igni7e
ID: 26153861
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
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 26153870
I think the above will not work. Please wait, i am re-checking this.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 13

Expert Comment

by:sameer2010
ID: 26153924
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
 

Author Comment

by:gdspeare
ID: 26153942
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
 

Author Comment

by:gdspeare
ID: 26153949
itkamaraj: - your code produces "San Antonio-San Antonio-"  Rather than the "South-San Antonio" value i expect.
0
 

Author Comment

by:gdspeare
ID: 26154358
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
 
LVL 41

Expert Comment

by:Sharath
ID: 26155354
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
 
LVL 41

Expert Comment

by:Sharath
ID: 26155365
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
 

Author Comment

by:gdspeare
ID: 26155584
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
 

Author Comment

by:gdspeare
ID: 26155613
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 26155677
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
 

Author Comment

by:gdspeare
ID: 26155705
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
 

Author Closing Comment

by:gdspeare
ID: 31671585
Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26155729
you are welcome.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question