Simple Access string problem.

T

TroubledProgrammer

Joined
Oct 28, 2011
Messages
5
Reaction score
0
I'm fairly new to programming and ive been assigned the job of building a database for my school. Besides the thousands of ridiculous problems access VBA presents I have an SQL question today. So i had a list of about 750 entries and 690 seperate names. Every name is structured like : Programmer, Troubled or PROGRAMMER, TROUBLED. First comma last. Ive narrowed down the list to just the unique names and here is what ineed help with. I have two collums, one for first name, one for last name. I putt all of the concatinated names in first name collumn to start me off. Ineed to make an sql statement that will sepeate every name into two parts and update both columns with the right name. I can do the rest of the statement I just need to know how in SQL to sepaate a sting based on a single character such as a comma.

PS keyboard here sucks half the keys dont press unless you hammer them down so i appologize for missing letters in words:)
 
I dont know how you would use SQL to split the string and then update, however you could use a simple select statment to get strings and then split them in VBA followed by an Update statment in SQL.
 
Do you want to split the string during insert or select. Coz during insert the value might come dynamically using whatever language you want.

to normally split using plsql, u can use

declare
nam varchar2 := "hello, world'
temp1 varchar2
temp2 varchar2
begin
temp1=substr(nam,0,nam.indexof(','))
temp2=substr(nam,nam.indexof(','),nam.length)
end

The syntax might be wrong coz i am not really a plsql guy but u get the idea. :)
 
Thanks for the replies guys. I did not think I was going to get any so I ended up solving it on my own after a long while of figuring out how sql works.

The full name string is in [Last Name]. This first one puts the first name into the [First Name] Column in Students
The reason for the many imediate ifs is because alot of typos were made when creating this list of names and some names did not even have comma separators. I expected them to repeat that so I just had to handle it :P
Code: 
UPDATE Students SET [First Name] = 
IIf(INSTR(TRIM([Last Name]),", ")>0,
RIGHT(TRIM([Last Name]),(LEN(TRIM([Last Name]))-INSTR(TRIM([Last Name]),",")-1)),
IIf(INSTR(TRIM([Last Name]),",")>0,
RIGHT(TRIM([Last Name]),(LEN(TRIM([Last Name]))-INSTR(TRIM([Last Name]),","))),
IIf(INSTR(TRIM([Last Name])," ")>0,
RIGHT(TRIM([Last Name]),(LEN(TRIM([Last Name]))-INSTR(TRIM([Last Name])," "))))));

This second one overwrites the [Last Name] with just the last name of the string.

Code: 
UPDATE Students SET [Last Name] = 
IIf(INSTR(TRIM([Last Name]),", ")>0,
Left(TRIM([Last Name]),InStr(TRIM([Last Name]),",")-1),
IIf(INSTR(TRIM([Last Name]),",")>0,
Left(TRIM([Last Name]),InStr(TRIM([Last Name]),",")-1),
IIf(INSTR(TRIM([Last Name])," ")>0,
Left(TRIM([Last Name]),InStr(TRIM([Last Name])," ")-1))));
 
Last edited:
Back
Top