Monday, March 26, 2012

Proper Names function

Hello: a nice simple question (I hope). Is there a MS SQL equivilant to PROPER (string) which would return "Fred Bloggs" from "FRED BLOGGS" and equally from "FrEd bLoggs" ? I cant find such ...
GerryNo one?

DECLARE @.x varchar(8000)
SELECT @.x = 'FRED BLOGGS'

SELECT UPPER(SUBSTRING(@.x,1,1))
+ LOWER(SUBSTRING(@.x,2,CHARINDEX(' ',@.x)-1))
+ UPPER(SUBSTRING(@.x,CHARINDEX(' ',@.x)+1,1))
+ LOWER(SUBSTRING(@.x,CHARINDEX(' ',@.x)+2,LEN(@.x)-CHARINDEX(' ',@.x)+2))|||Thanks. I was hoping for a nice simple built in function but hey-ho :)|||OK

CREATE FUNCTION udf_ProperName(@.x varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
RETURN UPPER(SUBSTRING(@.x,1,1))
+ LOWER(SUBSTRING(@.x,2,CHARINDEX(' ',@.x)-1))
+ UPPER(SUBSTRING(@.x,CHARINDEX(' ',@.x)+1,1))
+ LOWER(SUBSTRING(@.x,CHARINDEX(' ',@.x)+2,LEN(@.x)-CHARINDEX(' ',@.x)+2))
END
GO

SELECT dbo.udf_ProperName('FRED BLOGGS')
GO

DROP Function udf_ProperName
GOsql

No comments:

Post a Comment