Query for Numbers Only 1

[MichaelF81]

I have a field in a DB called "em_id" in the table "em". In this field the data is "lname, fname ID#". I want to pull from this field only the "ID#". Suggestions?




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

 

[gmmastros]

This query will return everything to the right of the last space. You really need to think about seperating the different pieces of information in to seperate fields.

Select ltrim(Right(em_id, CharIndex(' ', Reverse(em_id))))
From   em

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

I Had no wish to put the data into 1 field, I am not even supposed to be working on this database, I am covering until they hire someone. The client insists on it though.

Thanks for the code.




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

 

[gmmastros]

I understand about client requests. Perhaps you could convince them the store the data seperated AND joined together. It will certainly make the data more reliable while also satisfying their requirements.

In any case, I'm glad that I was able to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

It is duplicate data already, the same table has the first name and last name separate already. I honestly never interact with this client, the PM just said do it, and I am tired of arguing with her. Now I have to write these DTS Packages to concatenate the data they send us, and use your code to split it to send them. This is all testing still. What a waste of time.




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