Solved

Cannot concatenate a string in t-SQL

Posted on 2004-09-21
6
2,685 Views
Last Modified: 2012-05-05
I'm passing a comma separated string to my stored procedure, looping through it and trying to concatenate a string (sql). The loop is working as i can print each individual item as i go through it - but the variable that i am concatenating prints as blank after the loop - what is going wrong? here is the test code:

CREATE procedure wsp_search
@table varchar(32),
@list varchar(128),
@allwords int

AS

declare @sql varchar(4000),@term varchar(10), @pos int

begin
      set nocount on

      set @list = ltrim(rtrim(@list))+ ','
      set @pos = charindex(',', @list, 1)

      if replace(@list, ',', '') <> ''
      begin
            while @pos > 0
                  begin
                        set @term = ltrim(rtrim(left(@list, @pos - 1)))
                        print len(@term)
                        if len(@term)>0
                              begin
                                    --construct sql here
                                    select @sql = @sql  + '|||' + @term
                                    --select @sql=@term *this is ok but above is not!!!*
                                    --print @sql
                              end
                        set @list = right(@list, len(@list) - @pos)
                        set @pos = charindex(',', @list, 1)
      
                  end
      end
      print @sql
      --exec(@sql)      
end

GO
0
Comment
Question by:lildrc
6 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 65 total points
Comment Utility
you have "select @sql = @sql  + '|||' + @term"
but you never initialise @sql. It's initial value will be NULL, and concatinataing NULL with something else results in NULL. You just need to add an initial line of code to set @sql = '' before you go into the loop.

CREATE procedure wsp_search
@table varchar(32),
@list varchar(128),
@allwords int

AS

declare @sql varchar(4000),@term varchar(10), @pos int

begin
     set nocount on

     set @sql = ''

     set @list = ltrim(rtrim(@list))+ ','
     set @pos = charindex(',', @list, 1)

     if replace(@list, ',', '') <> ''
     begin
          while @pos > 0
               begin
                    set @term = ltrim(rtrim(left(@list, @pos - 1)))
                    print len(@term)
                    if len(@term)>0
                         begin
                              --construct sql here
                              select @sql = @sql  + '|||' + @term
                              --select @sql=@term *this is ok but above is not!!!*
                              --print @sql
                         end
                    set @list = right(@list, len(@list) - @pos)
                    set @pos = charindex(',', @list, 1)
     
               end
     end
     print @sql
     --exec(@sql)    
end
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 60 total points
Comment Utility
You're not initializing @sql. Try this:

declare @sql varchar(4000),@term varchar(10), @pos int
set @sql = ''
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
You're still fast on the draw, Bill!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
dead heat, I think!!
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
I got your goal- why do not use REPLACE function:

select '|||'+ replace ('1,2,3,4,5,6,7',',','|||')
--result |||1|||2|||3|||4|||5|||6|||7
0
 

Author Comment

by:lildrc
Comment Utility
looks good guys - that was puzzling me for ages! - i'll try it out at work tomorrow and issue the points.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now