Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-15 : 07:08:57
|
Mike writes "I have a legacy database with tables that have certain fields defined as Varchar(2000) but that have their contents Base64 encoded by the C# web page when it adds the rows.I know how to do Base64 encoding and decoding in C# but my question is this: is there any way to do decoding in SQL Server?Basically the main problem is this: we use one or two of these Base64 encoded columns for searching from the web site and if the case in the search string doesn't exactly match the string that Base64 encoded the search fails.With a normally encoded varchar column you could of course say:select * from artists where Lower(ArtistName) like '%jagger%' and you'd always get a successful search (if matching rows exist of course) but obviously I can't do an LCase on a Base64 encoded column so if the Artist had been encoded "Mick Jagger" a search for "jagger" or even "mick Jagger" would fail. So what I'm looking for is a function/ to decode the Base64 encoded column so I can do the comparison (or just view the data).eg:select select * from artists where Lower(DecodeBase64(ArtistName)) like '%jagger%' Can this be done? If so, I'd love to know how!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-15 : 07:14:21
|
| SQL Server 2000 supports user-defined functions (UDFs) and you could probably write one to do Base64 decoding, BUT: 1) the UDF must be written in T-SQL, which isn't very strong at twiddling bits2) you could create an extended stored procedure in C++ to do the decoding, and call it from the UDF, but it's not easy to write 3) constantly decoding the data to search it will be EXTREMELY slowI don't suppose you can extract the data, decode it, and insert it back into the table? |
 |
|
|
|
|
|
How to decode a Base64 encoded column in SQL Server 2000