URL Encode via SQL?
Shaniqua Jones
I'm using a particular field as a basis for forming a URL. For example,
the field might contain:
A1110541351(LQ#OBJim
...which would allow me to build the URL:
<http://hostname/process.aspx?id=A1110541351(LQ#OBJim>
The problem is that some of those characters in the ID aren't
URL-friendly. Is there a way to tell SQL to URLEncode the string before
returning it in the query?
Thanks!
Aaron [SQL Server MVP]
need to be encoded. You'd have to build your own function that steps
through each character in the string and rebuilds it, or runs a many-level
nested replace()).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Shaniqua Jones" <king...@Neverland.com> wrote in message
news:%23YD8b2Y...@TK2MSFTNGP12.phx.gbl...
Shaniqua Jones
> need to be encoded. You'd have to build your own function that steps
> through each character in the string and rebuilds it, or runs a many-level
> nested replace()).
I don't know enough SQL to do that. Instead, I'm adding a new field to
the table called "URLEncodedID". Then, I'll write a little .NET app (C#)
that runs through an updates each record.
It would be much easier if SQL Server had a way to call my C# method
directly from the SQL query. It seems like I read that such functionality
was going to be available in the next version of SQL Server. Is that true?
What's the timeline on that?
Jack D. Ripper
> It would be much easier if SQL Server had a way to call my C# method
> directly from the SQL query. It seems like I read that such functionality
> was going to be available in the next version of SQL Server. Is that true?
> What's the timeline on that?
Who knows:)
For your amusement check out:
http://tinyurl.com/22pxa
Dan Guzman
back-end? Apparently, you already have application code that can produce
the desired value. It seems to me you could simply call your C# method to
encode the value as needed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Shaniqua Jones" <king...@Neverland.com> wrote in message
news:u%23PFemgV...@TK2MSFTNGP11.phx.gbl...
Shaniqua Jones
news:uHIlI$hVEHA...@TK2MSFTNGP11.phx.gbl...
> But why store the encoded value in the database or do the encoding on the
> back-end? Apparently, you already have application code that can produce
> the desired value. It seems to me you could simply call your C# method to
> encode the value as needed.
>
Well, ya see, it's like this:
We have a CRM solution called Goldmine. The guy who administrates Goldmine
wants to send out a mass mail to every customer in the database. Each email
will be personalized with a special link for the recipient to click. If the
recipient's Goldmine-based ID is "H%7< jI/6", for example, the link
appearing in that recipient's email would be:
'http://ourdomain.com/Promo.aspx?GoldmineID=H%7< jI/6"
The email being sent is created by an external 3rd party application. This
emailer application knows how to create customized emails by pulling in
fields from the Goldmine database. For example, the email starts off by
saying:
"Dear <customer name>"
...where "customer name" is pulled from a 'name' field in the Goldmine
database. Things like names and addresses are easy, but what about the URL
above? Well, that URL won't work because the "GoldmineID" being pulled from
the database contains some characters that aren't URL friendly. Thus my need
to find a way to URLEncode that field before placing it in the email. Since
the 3rd party email app knows how to pull in fields via a SQL query,
URLEncoding would be easy if SQL supported it directly. Since it doesn't,
and since the 3rd party emailer has no concept of URLEncoding, presumably
it's up to me to stick a totally new field in the Goldmine database that
contains a URLEncoded version of the GoldmineID. I don't like the idea of
cluttering the database in this way, but I don't see any other alternatives.
Do you?
Thanks again...
Aaron W. West
all those outside the set [0-9A-Za-z]. Modify as desired, perhaps using
charindex for a more complicated set of non-encoded characters, or (if you
want to get crazy) substring of a bitmap of characters to convert or not
convert, represented as a 256-character string.
create function urlencode(@str as varchar(4000))
returns varchar(4000)
as
begin
declare @hex char(16)
declare @c char(1)
set @hex='0123456789ABCDEF'
declare @ostr varchar(4000)
set @ostr=''
declare @l int
set @l = 1
while @l <= len(@str)
begin
set @c = substring(@str,@l,1)
if @c between '0' and '9'
or @c between 'A' and 'Z'
or @c between 'a' and 'z'
set @ostr = @ostr + @c
else
set @ostr = @ostr + '%' +
substring(@hex,(ascii(@c)/16)+1,1)
+substring(@hex,(ascii(@c)&15)+1,1)
set @l=@l+1
end
return @ostr
end
go
select mydatabase.dbo.urlencode('H%7< jI/6')
H%257%3C%20jI%2F6
(1 row(s) affected)
(At first I tried using case when...then @c...else (hex string) end, but
noticed that my chars were padded with two spaces each. It seems the data
type of all return values from a case statement is expected/forced to be the
same...)
"Shaniqua Jones" <king...@Neverland.com> wrote in message
news:uNChJskV...@TK2MSFTNGP11.phx.gbl...
Shaniqua Jones
Thanks so much! I think this might be exactly what I'm looking for!
"Aaron W. West" <tall...@hotmail.NO.SPAM> wrote in message
news:nMycnSFrhbo...@speakeasy.net...
Steve Kass
It sounds like the Goldmine IDs just weren't designed to be included in
URLs. Could you add a uniqueidentifier column to the table containing
Goldmine ID values, or maintain a separate table associating each Goldmine
ID with a GUID (uniqueidentifier), using a default of NEWID() for the
uniqueidentifier? The string representation of a GUID is URL-friendly, so
you could use the uniqueidentifier in your URL without HTML-encoding it.
When the Goldmine ID is needed, it could be looked up from the
uniqueidentifier value.
Steve Kass
Drew University
"Shaniqua Jones" <king...@Neverland.com> wrote in message
news:uNChJskV...@TK2MSFTNGP11.phx.gbl...
Shaniqua Jones
>
> It sounds like the Goldmine IDs just weren't designed to be included in
> URLs. Could you add a uniqueidentifier column to the table containing
> Goldmine ID values, or maintain a separate table associating each Goldmine
> ID with a GUID (uniqueidentifier), using a default of NEWID() for the
> uniqueidentifier? The string representation of a GUID is URL-friendly, so
> you could use the uniqueidentifier in your URL without HTML-encoding it.
> When the Goldmine ID is needed, it could be looked up from the
> uniqueidentifier value.
Hi Steve... This is an interesting idea. I'll run this idea by our
Goldmine admin and see if he is open to the solution.