Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007

    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?

  2. #2
    Join Date
    Jan 2007
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts