Re: URL Encode via SQL?
From: Shaniqua Jones (kingOfPop_at_Neverland.com)
Date: 06/20/04
- Next message: Steve Kass: "Re: URL Encode via SQL?"
- Previous message: Joe Celko: "Re: Insert into 2 tables"
- In reply to: Aaron W. West: "Re: URL Encode via SQL?"
- Next in thread: Steve Kass: "Re: URL Encode via SQL?"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 19 Jun 2004 19:14:02 -0700
Hi Aaron,
Thanks so much! I think this might be exactly what I'm looking for!
"Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message
news:nMycnSFrhboUV0nd38DK-w@speakeasy.net...
> This seems to work. Not sure which characters you want encoded. I encoded
> 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" <kingOfPop@Neverland.com> wrote in message
> news:uNChJskVEHA.2564@TK2MSFTNGP11.phx.gbl...
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:uHIlI$hVEHA.3336@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...
>
>
>
- Next message: Steve Kass: "Re: URL Encode via SQL?"
- Previous message: Joe Celko: "Re: Insert into 2 tables"
- In reply to: Aaron W. West: "Re: URL Encode via SQL?"
- Next in thread: Steve Kass: "Re: URL Encode via SQL?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
