sanjshah12

asked on 9/12/2010

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,

Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Last Comment

sanjshah12

8/22/2022 - Mon

cyberkiwi

9/12/2010

You really should be doing the html/json encoding from the front end, be it c#,vb,asp,php or whatever.
But if you must from sql server, you can use replace, e.g.

select replace(replace(replace(returncol, '''', '`'), '&', '+'), '[', '(') AS returncol
from tbl

This just replaces single quote with backquotes, "&" with "+" etc

To turn a query into a stored procedure, here's a sample:

create procedure ShowAssets
@id int
AS
SELECT company, name1, name2, name3, name4, qty
FROM dbo.assets
WHERE id = @id
GO

Open in new window

JoeNuvo

9/12/2010

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

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

Open in new window

sanjshah12

9/12/2010

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?

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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

JoeNuvo

9/13/2010

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

sanjshah12

9/13/2010

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

ASKER CERTIFIED SOLUTION

JoeNuvo

9/13/2010

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

sanjshah12

9/13/2010

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

JoeNuvo

9/14/2010

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.

sanjshah12

9/14/2010

ASKER

Thansk Joe, good idea!