如何避免sql中的空格 [英] how to avoid white spaces in sql

问题描述

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER proc [dbo].[Presea_RefresherSMS] 
as
begin
   declare @Course varchar(20),

   @NoofStudents varchar(20),
   @Rowcount int,
   @batchid varchar(20),
   @CourseDate varchar(10),
   @OutputString VARCHAR(MAX) 
 
   select @CourseDate =CONVERT(VARCHAR(10), GETDATE(), 101)
   set @OutputString=''
 
   create table #TempTable (course varchar(10), Noofstudents varchar(10))
 
   begin tran
      declare courses Cursor for
 
      select cmn_minor_code as Course_Name,cbm_batch_id as Batch_ID from co_batch_master where cbm_active <> 'D' and cbm_batch_start_dt = '01/13/2014' and cmn_minor_code in('RFPFF','R-AFF','RFPFF_C')
 
      open courses
      fetch next from courses into @Course,@batchid
      while @@Fetch_status = 0 
      begin
         begin tran 
            declare studentcount cursor for
            select count(*) from batch_course_registration a,course_registration b
            where b.cr_bill_no = a.cr_bill_no and a.bcr_batch_id = @batchid and b.cr_active = 'A'

            open studentcount
            fetch next from studentcount into @Rowcount
            while @@Fetch_status = 0
            begin
               SET @OutputString=@OutputString +' ' +@Course +' '+ convert(varchar,LTRIM(@Rowcount))
               fetch next from studentcount into @Rowcount
            end
				
            close studentcount
            deallocate studentcount
         commit tran

         fetch next from courses into @Course,@batchid
      end
		
      close courses
      Deallocate courses
   commit tran

   select @OutputString
end

当我运行上面的商店程序显示输出如下



exec [Presea_RefresherSMS]

When i run the above store procedure shows output as follows

exec [Presea_RefresherSMS]

RFPFF                16 R-AFF                16 RFPFF_C              6

但是我希望输出如下

But i want output as follows

RFPFF 16 R_AFF 16 RFPFF_C 6

i希望在我的商店程序输出中避免使用空格。

我该如何修剪字符串。



请帮帮我。



问候，

Narasiman P.



缩进缩小

i want to avoid white spaces in my store procedure output.
for that how can i trim the string.

please help me.

Regards,
Narasiman P.

indentation reduced

推荐答案

Declare @String nvarchar(max)

set @String = 'RFPFF                16 R-AFF                16 RFPFF_C              6'
select @String, REPLACE(REPLACE(REPLACE(@String,' ','{}'),'}{',''),'{}',' ')

如果要删除末尾的空格，请使用 ltrim 或 rtrim 。

要替换所有空格，请尝试 SELECT REPLACE（fld_or_variable，''，''）

If you want to remove whitespaces at the end use ltrim or rtrim.
For replacing all whitespaces, try SELECT REPLACE(fld_or_variable, ' ', '')

这篇关于如何避免sql中的空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！