We help IT Professionals succeed at work.

Maintaining Character conversions

Wayne Atherton
Wayne Atherton used Ask the Experts™
on
Hi there,

I have attached a VBS script that I have developed to convert certain characters based on their Dec representation, to their ASCII equivalent, my customer is now wanting to maintain this manually.

However, I have no intention of letting them near the source code, and was wondering if this can be accomplished using SQL.

Any assistance would be appreciated.
convertChars.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Yes.  The VBScript functions you use Len(), Mid() and Asc() can be converted to the T-SQL functions LEN() or DATALENGTH(), SUBSTRING() and ASCII()
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
HI acperkins,

thanks for responding - I was looking to be able to maintain a table, I appreciate that i can replace the operations with their T-SQL equivalent, but I was looking more how to achieve storing the characters in a table.

For example, i need three rows -

ID | Character | ReplacementCharacter

What would be the best solution for passing in a string, finding it in the character col, and then replacing that character in the string with the one stored in ReplacementCharacter?

This is for an international payment file, so can contain thousands of transactions, so i need to keep the disk io down to a minimum, if possible.

cheers
Top Expert 2012

Commented:
And that is quite doable.  It will require some effort and I really do not have the time to do it right now.
Robert SchuttSoftware Engineer

Commented:
I think I can help.

So this will consist of 2 parts:
- maintaining the table, which should just be a form or web page that allows the user to add, remove or edit translations.
- a function that uses all character translations to translate a string to it's 'simplified' version.

Right from the start I want to ask you one thing: You use Asc() in the existing code and I thought that returns codes from 0 to 255, am I wrong? because you use codes > 255 in your translation table.
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
Hi all,

ok, i have another team working on the interface side of things, so this is covered. As far as I am aware, we only need to go up to 255 - numbers above this were put there in error.
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
The numbers are the UTF-16 bit representation.
Robert SchuttSoftware Engineer

Commented:
If they're making the interface side, are they also creating the database table?

Then all you would need is a function to convert a string using that table I guess.

Can you confirm that?

Or maybe it would be easier (and faster) to load the whole table in an array in your VBS and keep doing the conversion there.
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
robert,

that sounds like a plan, could you shed any code on how that whould work please?


Also, if this was possible, could I not use a static txt document, that is a lot easier, and cost effective to maintain?
Robert SchuttSoftware Engineer

Commented:
Slight miscommunication I suspect.

Do you mean just the last part? You would read the entire table (max 256 records) into an array in memory and use a direct index to replace the characters that need replacing, much like the function you had but without all the hardcoded asc codes.

If that's the way you want to go, please confirm the database structure that those other guys are working with, because now I'll assume that if they're making an interface for the translation maintenance application, they must have defined the table and columns.
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
Hi Robert

Cols are:

Id| character | replacementcharacter

Id is key, character is going to be the matched character with replacementcharacter being the one I replace.
Robert SchuttSoftware Engineer

Commented:
Ah, sorry. I just re-read your last line and yes, you can. Something like this?

224	à	a
225	á	a
226	â	a
227	ã	a
228	ä	a
229	å	a
230	æ	ae
231	ç	c
232	è	e
233	é	e
234	ê	e
235	ë	e
236	ì	i
237	í	i
238	î	i
239	ï	i
240	ð	o
241	ñ	n
242	ò	o
243	ó	o
244	ô	o
245	õ	o
246	ö	o
248	ø	oe
249	ù	u
250	ú	u
251	û	u
252	ü	u
253	ý	y
255	ÿ	y

Open in new window


You could read that in an array and use it instead of all the if...then's

I'll be out for a bit tonight but will get back to you later.
Software Engineer
Commented:
Ok, how about this:

Create the conversion list with the first script (which uses your original function), make it editable by the users but make sure the format stays the same (tab separated, empty string after the second tab will not be converted).

In the second script you find the functionality to convert a string using that file.
makelist.vbs
testconv.vbs
Wayne AthertonProduct Analyst Financial Messaging London

Author

Commented:
works a treat, thanks Robert