need geek help: ms sql

[gapertimmy]

i'm sure some nerd out there can help me...

 

i got a name field in a ms sql 7.0 db

where names are stored like such:

"Tim G Crawford"

 

I need to query and change it to the follwing format:

 

"Crawford, Tim G"

 

can anybody help me?

 

[bunglehead]

??? Can't help you with that, but if you have any questions about SPC, I can help!

[iain]

here's one way to do it:

 #!/usr/bin/perl

while (<STDIN>) {
chomp;
$_ =~ s/(.+\b)(.+\b)(.+)/$3, $1 $2/;
} 

 

[iain]

are the first, last, and middle initial in seperate defined fields, or is this one field?

[gapertimmy]

just one field

[iain]

That's a pain, I don't think there are built-in regular expressions for modifying text in that way, you would need to do a script.

 

If you have a perl install on your system and the DBD for MS SQL you could use the above to do the swap (putting in the sql commands instead of standard input). If need be, I could potentially do this from my unix setup here (I would have to hunt down the ms sql stuff, and you would have to trust me with access to your db, if your firewall allows), or you could dump all the data as a tab-delimited text file, run it through perl or something else to do the text swap, then do a load data back into the database. Or you could email me the txt file and I could do this.

[gapertimmy]

RARRRRRRR

perl is aid

[iain]

this is true.

[hakioawa]

Real ugly but something like. . . .

1) find the index of the second space and select it into a tmp table

 

SELECT pkey, my_column, PATINDEX( '% _ %', my_column) as start, LEN(my_column) as length

FROM my_table INTO my_tmp_table

 

2) now go back

 

 

UPDATE my_table t

SET t.my_column =

(

select SUBSTRING(tmp.my_column, tmp.start, tmp.length) + ', ' + SUBSTRING(tmp.my_column, 0, tmp.start)

from tmp_table tmp where t.pkey=tmp.pkey)

[Gaper_Jeffy]

This works:

 

UPDATE names

SET nameColumn =

RIGHT(nameColumn,CHARINDEX(' ',REVERSE(nameColumn))-1) + ', ' +

LEFT(nameColumn,LEN(nameColumn)-CHARINDEX(' ',REVERSE(nameColumn)))

 

 

 

 

 

Here's a proof-of-concept you can copy-and-paste into Query Analyzer:

 

CREATE TABLE names (

nameColumn VARCHAR(50)

)

 

INSERT INTO names VALUES ('Timmy G Craford')

INSERT INTO names VALUES ('Gaper W Jeffey')

INSERT INTO names VALUES ('Joe Bachy')

INSERT INTO names VALUES ('John Ashcroft')

GO

 

SELECT * FROM names

GO

 

UPDATE names

SET nameColumn =

RIGHT(nameColumn,CHARINDEX(' ',REVERSE(nameColumn))-1) + ', ' +

LEFT(nameColumn,LEN(nameColumn)-CHARINDEX(' ',REVERSE(nameColumn)))

GO

 

SELECT * FROM names

 

 

As always (and I'm sure you're doing) make sure to backup your data first and consider working off a test table or a test column. If any names don't contain spaces (eg they're just 'Bob') the above script will throw an error. If you need it to not do so you should add a WHERE clause which filters out names that don't contain space, eg WHERE nameColumn like '% %'.

 

Hope this helps,

 

Jeff

 

 

 

[Attitude]

Gaper_Jeffy said:

Here's a proof-of-concept you can copy-and-paste into Query Analyzer:

Is Query Analyzer a form of Gay-dar?