Maintaining Character conversions

Yes.  The VBScript functions you use Len(), Mid() and Asc() can be converted to the T-SQL functions LEN() or DATALENGTH(), SUBSTRING() and ASCII()

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

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

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.

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.

The numbers are the UTF-16 bit representation.

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.

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?

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.

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.

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

Select allOpen 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.

works a treat, thanks Robert