Solved

Replace characters in string in a Stored Procedure

Posted on 2010-09-12
9
542 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now