Solved

Replace characters in string in a Stored Procedure

Posted on 2010-09-12
9
547 Views
Last Modified: 2012-05-10
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,
0
Comment
Question by:sanjshah12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33656311
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

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33656965
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

0
 

Author Comment

by:sanjshah12
ID: 33658433
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

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33659984
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
0
 

Author Comment

by:sanjshah12
ID: 33661193
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
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 250 total points
ID: 33668420
Oh.. sorry, I mistake see your comma as part of the string.

change code to be as below
(which just replace comma with + )


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

0
 

Author Comment

by:sanjshah12
ID: 33669032
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
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33670000
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.
0
 

Author Comment

by:sanjshah12
ID: 33670339
Thansk Joe, good idea!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question