SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to replce comma to ','
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 01/22/2013 :  07:35:44  Show Profile  Reply with Quote
Hi
all I am trying to replace comma (,) to (',')
but not getting this how to do this


declare @Chain varchar(max)
set @Chain= 'Barnetts South Africa,Bradlows South Africa'

select @Chain = replace(@Chain, ',', '','')
print @Chain

its giving me error ..Please advise me how to pix this

Vijay is here to learn something from you guys.

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/22/2013 :  07:45:10  Show Profile  Reply with Quote
Need couple of extra single quotes. You can escape a single quote with another single quote
replace(@Chain, ',', ''',''')
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 01/22/2013 :  08:00:34  Show Profile  Reply with Quote
Thank it is working. But I am having problem when I pass this
into my filter condition it does not retrieve records ...
I am not sure whether I am passing this correctly..

Below is the procedure and its Script.. I have pointed out the
variable in bold where I want to pass this string and wanna output..

exec [dbo].[sp_MdlModuleWise] ' Furniture Retail Module 1' ,'Barnetts South Africa,Bradlows South Africa'



ALTER procedure [dbo].[sp_MdlModuleWise] @Module varchar(100),@Chain varchar(max)
as


set @Chain = replace(@Chain, ',', ''',''')
set @Chain= ''''+@Chain+''''
print @Chain


      create table #resullt ( ModuleName varchar(50),
                              chain varchar(100),
                              [Pass] int,
                              fail int)
                              
      select  distinct Userid,
                      ID,
                      pass,
                      modulename,
                      chain_description  into #temp 
                      from [dbo].[v_ModuleReport] where  pass='yes' and ModuleName is not null
                                                  and ltrim(rtrim(modulename)) in (SELECT ltrim(rtrim([Name])) FROM [moodle].[dbo].[mdl_module]) 
                                                  and chain_description in (select distinct chain_description  from v_hr_moodle where chain_description is not null)
                                                  and userid is not null


	;with cte 
	AS 
	(
		select *, row_number () over (partition by userid,modulename,chain_description order by id ) rn  from #temp
	)

	select userid as userid,
	       modulename,
	       chain_description,
	       MAX(rn) as [Max] into #passed  
	       from cte group by userid,
	                         modulename,
	                         chain_description having  MAX(rn)=6


	insert into #resullt(ModuleName,
	                     chain,
	                     [Pass],
	                     fail) 
	             select 
	                     modulename,
	                     chain_description,
	                     COUNT(1),
	                     0 
	                     from  #passed group by 
	                                   modulename, 
	                                   chain_description


	;with cte 
     AS 
    (
         select *, row_number () over (partition by userid,modulename,chain_description order by id ) rn  from #temp
     )

      select userid as userid,
             modulename,
             chain_description,
             MAX(rn) as [Max] into #NotPassed 
             from cte   group by  userid,
								  modulename,
								  chain_description having  MAX(rn)<6


      insert into #resullt(ModuleName,
						   chain,
						   [Pass],
						   fail)  
				   select modulename,
				          chain_description,
				          0,
				          COUNT(1) 
				          from #NotPassed group by modulename, 
												  chain_description
												 									  
           select ModuleName,
				  chain,
				  sum(pass)  Completed,
				  sum(fail) NotCompleted from #resullt where ModuleName =(@Module) 
				                                       and chain in (@Chain)

 
				                                       group by ModuleName,chain
				 
drop table #temp 
drop table #passed
drop table #resullt
drop table #NotPassed

GO


Vijay is here to learn something from you guys.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/22/2013 :  08:08:40  Show Profile  Reply with Quote
quote:
and chain in (@Chain)

You cannot use a variable in the IN clause to compare against two or more values. You should split your comma-separated tokens into a table and the join with that table. There are several string splitter functions available (on SQLTeam and elsewhere).

You could also do something like this:
and ','+@Chain+',' like '%,'+cast(chain as varchar(32))+',%'
The preferable approach would be to use the string splitter rather than this - mainly because with this method, SQL Server will not be able to use any indexes you may have on the chain column.

In either case, don't add the extra single quotes to your search string.
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 01/22/2013 :  08:12:52  Show Profile  Reply with Quote

Thank you very much ...

Vijay is here to learn something from you guys.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/22/2013 :  08:17:28  Show Profile  Reply with Quote
You are quite welcome - glad to be of help.

There is a discussion of string splitters in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/22/2013 :  10:03:09  Show Profile  Reply with Quote
here another method which is XML based

http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html

and the below is based on udf



http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000