Unpacking Name to Name, Initials

[MichaelRussell]

Dear All,
Firstly, I should say I'm using ASA, not ASE.

Can you help deal with this problem?
I have a table called Trainers:
Trainer char(5)
Tname char(30)

The col Tname contains data like:
'A B Smith'
'N Tinkler'

I need this data to be reformatted thus:
'Smith A B'
'Tinkler N'

This could be achieved by adding a third col, say Initials, if you think it's necessary.

Thanks in advance for your help.

Regards

Michael

 

[JMCraig]

If the data is as you've shown, you could solve this by taking the approach that you'd find the last blank in the string and cut off everything to the right of that and put that at the beginning of the string followed by a blank and everything to the left of the last blank.

If you have a difficult time creating an entirely general solution, it wouldn't be hard to write a loop that'd go through your data one row at a time (or use a cursor).

I'm not as familiar with ASA, but it apparently has string functions that would allow you to find the index of a given character (you'll need the right-most occurrance of blank), take apart the string, and concatenate the parts back together in a different order. Here's a link to the on-line docs if that's of any use:

http://sybooks.sybase.com/aw.html

Choose your version's Reference manual, navigate to:

SQL Functions
String Functions

BOL,

John



It sounds to me like you'd be happier in the long run with separate columns. J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net