sanjshah12
asked on
Replace characters in string in a Stored Procedure
Hi,
I am creating an ajax output from an stored procedure but cannot include from any text within the fields e.g. [ & or '
is there a function that I can use within the stored procedure to replace or encode the strings?
Also I have this query but would like to convert to a Stored Procedure - is this possible>
SELECT company, name1, name2, name3, name4, qty
FROM dbo.assets
WHERE id = 1
Thanks,
I am creating an ajax output from an stored procedure but cannot include from any text within the fields e.g. [ & or '
is there a function that I can use within the stored procedure to replace or encode the strings?
Also I have this query but would like to convert to a Stored Procedure - is this possible>
SELECT company, name1, name2, name3, name4, qty
FROM dbo.assets
WHERE id = 1
Thanks,
Since you are using ajax.
I guess you may need escape character for ' , " and \
so, you may create function for this to include in store procedure
I guess you may need escape character for ' , " and \
so, you may create function for this to include in store procedure
CREATE FUNCTION dbo.java_escape
(@inputstr varchar(50))
RETURNS varchar(100)
AS
BEGIN
RETURN replace(replace(replace(@inputstr, '\', '\\'), '''', '\'''), '"', '\"')
END
GO
CREATE PROCEDURE [sp_name_here]
....
AS
SELECT
...
,dbo.javaescape(name1) as Name1
, ...
FROM
...
WHERE
...
GO
ASKER
Thanks Joe, I like the function,
if I created a procedure like the code, would the output be one string or would this be severla records, I'm looking for just one string?
if I created a procedure like the code, would the output be one string or would this be severla records, I'm looking for just one string?
create procedure ShowAssets
@id int
AS
SELECT "[" +
'"' + dbo.java_escape(Name1) + '",',
'"' + dbo.java_escape(Name2) + '",',
'"' + dbo.java_escape(Name3) + '",',
'"' + dbo.java_escape(Name4) + '",',
'"' + dbo.java_escape(Name5) + '"'
+ '],'
FROM dbo.assets
WHERE id = @id
GO
Yes, your code is ok
note : please change the size of varchar in function to match your field.
now I put it with length of 50 characters
note : please change the size of varchar in function to match your field.
now I put it with length of 50 characters
ASKER
Joe,
I have tried this but I beleve these are being sent back as individual records is it possible to collate and send back as just one string?
Thanks
I have tried this but I beleve these are being sent back as individual records is it possible to collate and send back as just one string?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Joe,
that's better, can I use a for loop until eof to output all the records as one string, at present each line is a record?
Thanks
that's better, can I use a for loop until eof to output all the records as one string, at present each line is a record?
Thanks
I'm not very sure for BEST way to perform this on database.
if it's my own task, I will handle this on the application instead.
suggest you to ask new question regarding output all records as one string.
so, other expert can give some light.
if it's my own task, I will handle this on the application instead.
suggest you to ask new question regarding output all records as one string.
so, other expert can give some light.
ASKER
Thansk Joe, good idea!
But if you must from sql server, you can use replace, e.g.
select replace(replace(replace(re
from tbl
This just replaces single quote with backquotes, "&" with "+" etc
To turn a query into a stored procedure, here's a sample:
Open in new window