ive been working on this script today and cannot for the life of me get the following employee name format correct...

the data in the field is currently "lastname~firstname" in employee table... i am trying to write a select statement that will take the data and remove the "~" (tilde) and replace it with a "," (comma).

how do i get "lastname~firstname" from employee table to display in "lastname,firstname" format?

actually i figured it out guys... chcek it out and feel free to use it in the future...

-- this script queries employee table for lastname~firstname
-- birthday in mm/dd/yyyy format
-- ssn is formatted with dashes. e.g. 123-45-6789
-- lastname~firstname will convert to lastname,firstname

declare @SSN as varchar(11)
select replace(replace(name,'~',','), CHAR (10), '') as name,
convert(char(10), birthdate, 101) as bday,
Left(SSN,3)+'-'+Substring(SSN,4,2)+'-'+Substring(SSN,6,4) as ssn
from employee
where status = 'A'
order by name


the above converts the birthdays from yyyy-mm-dd to mm/dd/yyyy, ssn from 123456789 to 123-45-6789 and lastname~firstname to lastname,firstname and of course limits results to those employees that are "ACTIVE" status