Unanswered: select statement to replace a character in field?
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
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