I need a little help with the replace function can anyone give suggestions?

  • I have been working on a query that has 4 the first is length of name second is title third is first and last name initial and my last column need to be the employee phone number without the () I have to use the replace function twice and i can not find examples of this function can someone show me how to add it into the query i already have? here is the query

    SELECT 'Your full name is ' + CAST(LEN(FirstName) + LEN(LastName) AS VARCHAR(12)) + ' character(s).' AS [Length of Employee Name],

    CASE TitleOfCourtesy WHEN 'Mr.' THEN 'Mister ' WHEN 'mrs. ' THEN 'Miss' WHEN 'Ms.' THEN 'Miss ' WHEN 'Dr.' THEN 'Doctor ' ELSE '' END + TitleOfCourtesy + LastName

    AS title, SUBSTRING(FirstName, 1, 1) + SUBSTRING(LastName, 1, 1) AS [First and last Name Initials]

    FROM dbo.Employees

  • bmgtone (4/20/2015)


    I have been working on a query that has 4 the first is length of name second is title third is first and last name initial and my last column need to be the employee phone number without the () I have to use the replace function twice and i can not find examples of this function can someone show me how to add it into the query i already have? here is the query

    SELECT 'Your full name is ' + CAST(LEN(FirstName) + LEN(LastName) AS VARCHAR(12)) + ' character(s).' AS [Length of Employee Name],

    CASE TitleOfCourtesy WHEN 'Mr.' THEN 'Mister ' WHEN 'mrs. ' THEN 'Miss' WHEN 'Ms.' THEN 'Miss ' WHEN 'Dr.' THEN 'Doctor ' ELSE '' END + TitleOfCourtesy + LastName

    AS title, SUBSTRING(FirstName, 1, 1) + SUBSTRING(LastName, 1, 1) AS [First and last Name Initials]

    FROM dbo.Employees

    Something like?

    , REPLACE(REPLACE(phone,'(','')), ')','')

  • If you want to remove the parens, I'd use the code that's listed above. If you need more reformatting, you might use CHARINDEX/PATINDEX to find them and then SUBTRING things around, but I'd avoid that if possible.

  • When i try it that way it says the replace function requires 3 arguments

  • Could you post your code?

  • I fixed it thanks

    SELECT 'Your full name is ' + CAST(LEN(FirstName) + LEN(LastName) AS VARCHAR(12)) + ' character(s).' AS [Length of Employee Name],

    CASE TitleOfCourtesy WHEN 'Mr.' THEN 'Mister ' WHEN 'mrs. ' THEN 'Miss' WHEN 'Ms.' THEN 'Miss ' WHEN 'Dr.' THEN 'Doctor ' ELSE '' END + TitleOfCourtesy + LastName

    AS title, SUBSTRING(FirstName, 1, 1) + SUBSTRING(LastName, 1, 1) AS [First and last Name Initials], REPLACE(REPLACE(HomePhone, '(', ''), ')', '')

    AS HomePhone

    FROM dbo.Employees

  • Glad it's working and thanks for posting what worked for you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply