April 20, 2015 at 8:52 am
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
April 20, 2015 at 8:59 am
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 querySELECT '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,'(','')), ')','')
April 20, 2015 at 9:09 am
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.
April 20, 2015 at 9:13 am
When i try it that way it says the replace function requires 3 arguments
April 20, 2015 at 9:14 am
Could you post your code?
April 20, 2015 at 9:14 am
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
April 20, 2015 at 9:27 am
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