April 23, 2003 at 11:12 am
I have a table with columns job_name,department,city.They are strings like
job_name=software engineer
city=new hampshire
department= clinical labs.This is one row in the table which is having tousands of rows.
I wanted to convert this data to the format Software Engineer,New Hampshire,Clinical Labs
The first letter of the each word should be of capital.
Can anyone tell me a stored procedure or some other way to acieve this..
April 23, 2003 at 11:28 am
You might try this UDF:
This script came from: Angela But -- abut@rdns.com.au
CREATE FUNCTION fnTitleCase (@instring nvarchar(256))
RETURNS nvarchar(256)
AS
--This function will evaluate an input string and convert it to title-case format.
-- It uses the delimchars parameter to determine what are the triggering characters
-- to indicate capitalization of the subsequent character
BEGIN
DECLARE @strptr INT
DECLARE @outstring nvarchar(255)
DECLARE @strChar char(1)
DECLARE @delimchars nvarchar(256)
SET @outstring = ''
SET @strptr = 0
IF @delimchars is NULL
/* Plug in typical upper-case delimiters. */
/* NOTE: For localization purposes, you may wish to modify this */
SET @delimchars = ' ''-_.,'
/* Loop through the entire string */
WHILE @strPtr < len(RTRIM(@instring))
BEGIN
SET @strptr = @strptr + 1
SET @strchar = SUBSTRING(@instring,@strptr,1)
IF @strptr = 1 /* Assume that first character must always being
upper-cased*/
SET @outstring = UPPER(@strchar)
ELSE /*Check for other upper-case trigger character */
IF CHARINDEX(SUBSTRING(@instring,(@strptr - 1),1),@delimchars) > 0
-- SET @outstring = SUBSTRING(@outstring,1,@strptr)+UPPER(@strchar)
SET @outstring = @outstring+UPPER(@strchar)
ELSE
SET @outstring = @outstring+LOWER(@strchar)
END
RETURN @outstring
END
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 23, 2003 at 12:51 pm
This doesn't meet my requiremnts.Can u tell me the procedure for all the records not for a single entry.Thanks
April 23, 2003 at 2:03 pm
There was an article by Steve Jones, called Tame Those Strings, Part 7, that probably needs to be only slightly modified for what you're trying to do.
http://www.sqlservercentral.com/columnists/sjones/tamethosestringspart7.asp
April 23, 2003 at 2:06 pm
On a similar note, if my SQL 7 database is set up to be case insensitive, how would I go about checking whether a column is all upper case or not?
If I did just a
where address1 = upper(address1)
I get everything back
TIA
Andre
April 23, 2003 at 4:40 pm
Thnak you very much for your help.I appreciate ur help..
April 23, 2003 at 6:02 pm
AndreQ try
Select Top 10
Case When Convert(Varbinary,Short_Descr)=Convert(Varbinary,Upper(Short_Descr))
Then 'Yes'
Else 'No' End
From Item
April 24, 2003 at 6:25 am
Cool! I have been trying to use binary_checksum to do this, with varying results. Never thought about converting to varbinary.
Thanks!
Jay Madren
Jay Madren
April 24, 2003 at 9:49 am
Thanks Jay. It worked really well.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply