Microsoft SQL statement

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Please help.

Im trying to merge two colums together and the put the result in a new column and within the merge trim one column down to remove the middle name:

Current Columns:
Surname
blake
Forenames
james david

New Column to read:
User_ID
james blake

what is the statement to trim the column and then merge the two columns together to get the desired result of one column with full name.

Any help would be appreciated...

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

The problem you face is parsing the Forenames field. Not everyone will have 2 forenames, and unless you have a structure to this field (poor normalization) then extracting the correct bits may be a problem.

I don't know MSSQL, but standard SQL would use substring (string from pattern for escape) and string concatenation.

And the fact that you want the *first* forename may not make everyone happy. For the first 18 years of my life I was known by my second forename, and the family continues to use it!

Harry.

 

Thats a good help thanks, but im taking this task on for reference purposes on another table. Do you know the scripting that is needed?

 

Er - what scripting? You asked for a statement, so I assumed you were attempting to do this in one update line.

If you are going to write stored procs then obviously you can handle name variations much better, but it could get complex!

You say

. What do you mean by that? I admit that I'm a tad confused on what you are trying to do now!

Harry.

 

Sorry to confuse i meant to say statement not script.

Things have changed now as i have a new table from an active directory with three columns User ID, Surname, Forename.

So what i need to do is populate the user_id column on employees table with the user_id data from the Active directory table by matching the surname columns from the two tables and using the LIKE term for the fornames column as the AD table only has 1 first name in the column where as the employees table has first and middle in the fornames column.

Can you help?

regards

 

I'd suggest you start with a join on the two tables to see if the results are what you expect. If these two tables have been populated at different times in different ways then you may be shocked at the differences between them!

When the join is working correctly then you should be able to do an update. MSSQL seems to have an extension to SQL99 in the shape of 'FROM' which will probably simplify the statement, but as I don't have MSSQL I don't know exactly how it works.

Harry.