CODEWITHSUNDEEP
sql-servert-sql
Sushma Gowda
Sushma Gowda

Reputation: 3

how to replace symbol " ," with " ', ' " in sql?

I am writing a SQL query such that it has to replace a , to a ',' to the string that am passing. The string am passing is like jack,john,tony but the output am getting is empty table only with column header.

can you provide a answer for this.

CREATE PROCEDURE [dbo].[usp_GetBillingSystem]
(
@BillingCode VARCHAR(300)
)
AS
BEGIN 
declare @ListOfPlanID varchar(30)
declare  @trimmed varchar(30)
declare  @replaced varchar(30)
set @ListOfPlanID =@BillingCode


If  IsNull(@ListOfPlanID,'')='' return 
If LEN(@ListOfPlanID)=0 return 
SET @trimmed=LTRIM(RTRIM(@ListOfPlanID))
set @replaced=''''+REPLACE(@trimmed,',',''',''')+''''   
select * from BillingSystem where BillingCode IN (@replaced)
END

In vb.net i written a code for get datatable from above sql query this way 

Public Function getBillingSystemAttributesInfo(ByVal ConnectionString As String, ByVal                      BillingCode As String) As DataTable
Dim conn As SqlConnection
Dim sqlcmd As SqlCommand
Dim dtBillingSystem As DataTable
Try
conn = New SqlConnection(ConnectionString)
conn.Open()
sqlcmd = New SqlCommand("usp_GetBillingSystem", conn)
With sqlcmd

.CommandType = CommandType.StoredProcedure


 .Parameters.Add("@BillingSystemCode", SqlDbType.VarChar, 300)
 .Parameters("@BillingSystemCode").Value = BillingSystemCode

  dataadapter = New SqlDataAdapter()
  dataadapter.SelectCommand = sqlcmd
  dtBillingSystem = New DataTable("BillingSystem")
  dataadapter.Fill(dtBillingSystem)
   End With
   Catch ex As Exception
    Throw ex
    Finally
    conn.Close()
    sqlcmd = Nothing
    End Try
    Return dtBillingSystem
End Function

Upvotes: 0

Views: 3184

Answers (3)

Satheesh Variath
Satheesh Variath

Reputation: 680

The issue is that you cannot pass a variable to IN operator, You have to use the dynamic SQL here

CREATE PROCEDURE [dbo].[usp_GetBillingSystem]
(
@BillingCode VARCHAR(300)
)
AS
BEGIN 
declare @ListOfPlanID varchar(30)
declare  @trimmed varchar(30)
declare  @replaced varchar(30)
set @ListOfPlanID =@BillingCode


If  IsNull(@ListOfPlanID,'')='' return 
If LEN(@ListOfPlanID)=0 return 
SET @trimmed=LTRIM(RTRIM(@ListOfPlanID));

set @replaced=''''+REPLACE(@trimmed,',',''',''')+'''';
declare @qry nvarchar(max);
set @qry = 'select * from BillingSystem where BillingCode IN (' + @replaced + ')';
exec sp_executesql @qry
end

Upvotes: 1

Aijaz Chauhan
Aijaz Chauhan

Reputation: 1649

You don't need to replace any thing. If you are using "IN" for dynamic values you need to prepare a query by your self dynamically.

replace your code by 

CREATE PROCEDURE [dbo].[usp_GetBillingSystem]
(
   @BillingCode VARCHAR(300)
)
AS

BEGIN 

  exec('select * from BillingSystem where BillingCode IN ('+@BillingCode+')')

END

it will surely work

Upvotes: 0

smn_onrocks
smn_onrocks

Reputation: 1342

i am giving you a function that work as replacer but it is in postgresql so you modify according to your database language..

CREATE OR REPLACE FUNCTION public.fn_replacer(to_rep character varying, by_rep character varying, str character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
res varchar2(100);

BEGIN
    for i in 1.. length(str)
    loop
    res:= res||(case when substr(str,i,1)=TO_rep then by_rep else substr(str,i,1) end); 
    end loop;

return res;
end $function$;

Upvotes: 0

Related Questions