Concantanation Problem 1

[MichaelF81]

I have 2 DTS packages for a client, and 1 of them concatenates 3 fields into 1 (and works fine), and the other I need to take the concatenated field and split it up to only grab 1 part of it.

My code concatenates as follows "lastname, "firstname MI" "em_id" (middle initial is part of the first name). I need to then only grab the "em_id" for a query. Ideas?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[ESquared]

Concatenating strings together and pulling them back apart doesn't seem like the best way to get the job done. What are you trying to do?

If you insist on this, though:

SELECT Right(ConcatField, CharIndex(' ', Reverse(ConcatField) + ' ') - 1)

This will grab the rightmost group of characters, everything after the last space in the field (or all characters if there's no space).

 

[MichaelF81]

I will try this in the morning and let you know the results. This is perfext since it is

(shortened code

em_id = name_last + ', ' + name_first + ' ' + em_id

There is a space directly before the actual em_id.

This is not my choice by the way. I just work on the DB side, there is a third party software involved and it can only show one field the particular area. They want it to be the above. I can not argue with the Client, so I do what they, no matter how much redundancy there is.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

That did the trick. Thanks a lot!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Ok, next problem

UPDATE afm.em
SET u_deleteflag = 1
WHERE (Select Distinct Right(em_id, CharIndex(' ', Reverse(em_id) + ' ') - 1) from afm.em) NOT IN (SELECT DISTINCT Right(em_id, CharIndex(' ', Reverse(em_id) + ' ') - 1) from AAA_import)

gives me the following

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

never mind, I got it working




"Adults are just obsolete children and the hell with them." - Dr. Seuss