Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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,
0
sanjshah12
Asked:
sanjshah12
  • 4
  • 4
1 Solution
 
cyberkiwiCommented:
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
 
JoeNuvoCommented:
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
 
sanjshah12Author Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
JoeNuvoCommented:
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
 
sanjshah12Author Commented:
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
 
JoeNuvoCommented:
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
 
sanjshah12Author Commented:
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
 
JoeNuvoCommented:
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
 
sanjshah12Author Commented:
Thansk Joe, good idea!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now