Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replace characters in string in a Stored Procedure

Posted on 2010-09-12
9
Medium Priority
?
549 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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