October 10, 2002 at 5:47 pm
I believe the I saw a function that would do this but don't remember what it is.... can anyone help??
Thanks
Steve Johnson
Steve Johnson
October 10, 2002 at 6:29 pm
Of every word? If the first word, use substring and upper() and lower()
select upper(substring(col,1,1)) + substring( col,2)
Steve Jones
October 10, 2002 at 7:12 pm
That or if you want and I can find. I started on an SP/Function that did proper case that would take into account things such as O'Hare instead of O'hare and several other english comforming cases and handled strings as well as single words. But it is unfinished and I may have deleted along the way since I haven't thought about it for at least a year. Otherwise Steve is the solution to look at.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 10, 2002 at 7:12 pm
Simulate Visual Basic 6's StrConv(string, vbProperCase) as a function in TSQL.
October 11, 2002 at 9:53 am
select upper(substring(col,1,1)) + substring( col,2) this will work for what I need. Thanks guys....
Steve
Steve Johnson
Steve Johnson
October 11, 2002 at 10:13 am
Antares686 I will take you up on your offer... I need the O'Hare functionality
Thanks
Steve Johnson
Steve Johnson
October 11, 2002 at 11:23 am
Alright, will you email to remind me to take a look at work whe I get back on Tuesday, I am home now and don't have time to retrieve as gotta go get my son and make several appointments here in the next few. Also, email me at jtravis@carolina.rr.com and if I get a chance tonight or this weekend I may dial in and get it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 14, 2002 at 7:13 pm
Alright here is the work I started and for the most part I know it was working, I just did not finish out some special stuff. Take a look at it, if SQL 2K easily make a function if not then you will need to use a cursor to clean the data. However, keep in mind this is a use at your own risk so be carefull. I may post it if anyone thinks it is worth it but one day I plan to finish out or maybe someone here wants to throw in and add to it.
Here it is
/* Copyright @2001 James Travis */
CREATE PROCEDURE ip_CorrectCase
@ValInVARCHAR(8000), --SET @ValIn = 'j a keller ,martin l jones, patricia stanz. hello there.'
@LookForVARCHAR(1) = ' ', --The value to look for such as @LookFor = '.' otherwise it looks for ' '
@ValOutVARCHAR(8000) OUTPUT --This is our data on its way out
AS
DECLARE @SplitAtNUMERIC
DECLARE @SplitAt2NUMERIC
DECLARE @ValTempVARCHAR(8000)
DECLARE @valhVARCHAR(8000)
DECLARE @ValFVARCHAR(8000)
DECLARE @ValLVARCHAR(8000)
SET @ValTemp = ''
SET @ValL = ''
WHILE CHARINDEX(@LookFor, @ValIn, 1) > 0
BEGIN
IF LEFT(@ValIn , 1) LIKE '[a-z]' OR LEFT(@ValIn , 1) LIKE '[A-Z]' --Make sure is an Alpha Character
BEGIN
SET @SplitAt = CHARINDEX(@LookFor, @ValIn, 1) --Find the position of the LookFor Item
SET @ValF = LEFT(@ValIn, @SplitAt - 1) --Set @ValF equal to everything in fron of the start position
WHILE CHARINDEX('-', @ValF, 1) > 0 --Keep doing this along as there are hyphens in the string
BEGIN
IF LEFT(@ValF , 1) LIKE '[a-z]' OR LEFT(@ValF , 1) LIKE '[A-Z]' --Make sure first character is alpha
BEGIN
SET @SplitAt2 = CHARINDEX('-', @ValF, 1) --Find the hyphen position
SET @valh = LEFT(@ValF, @SplitAt2 - 1) --Strip out all that is before the hyphen
IF LEFT(@ValH, 2) IN ('o''', 'mc') --Check for special instances suc as McBride
BEGIN
SET @valh = UPPER(LEFT(@ValH, 1)) + LOWER(RIGHT(LEFT(@ValH, 2),1)) + UPPER(RIGHT(LEFT(@ValH, 3),1)) + LOWER(RIGHT(@ValH, LEN(@ValH) -3)) --Correct case for special instances
END
ELSE --Do this if no special instances
BEGIN
SET @valh = UPPER(LEFT(@ValH, 1)) + LOWER(RIGHT(@ValH, LEN(@ValH) -1)) --Make first character a capital
END
SET @ValTemp = @ValTemp + @valh + '-' --Concate to Temp Value with hyphen
SET @ValF = RIGHT(@ValF, LEN(@ValF) - @SplitAt2) --Keep only the stuff after the hyphen
END
ELSE --If first character is non-alpha
BEGIN
SET @ValTemp = @ValTemp + LEFT(@ValF, 1) --Concate first character to Temp Value
SET @ValF = RIGHT(@ValF, LEN(@ValF) - 1) --Keep only stuff after non-alpha character
END
END
--If all hyphens are gone deal with overs
WHILE LEFT(@ValF , 1) NOT LIKE '[a-z]' OR LEFT(@ValF , 1) NOT LIKE '[A-Z]' --Make sure first character is alpha
BEGIN
SET @ValTemp = @ValTemp + LEFT(@ValF, 1) --Concate first character to Temp Value
SET @ValF = RIGHT(@ValF, LEN(@ValF) - 1) --Keep only stuff after non-alpha character
END
IF LEFT(@ValF, 2) IN ('o''', 'mc') --Check for special instances
BEGIN
SET @ValF = UPPER(LEFT(@ValF, 1)) + LOWER(RIGHT(LEFT(@ValF, 2),1)) + UPPER(RIGHT(LEFT(@ValF, 3),1)) + LOWER(RIGHT(@ValF, LEN(@ValF) -3)) --Correct case for special instances
END
ELSE --Do this if no special instances
BEGIN
SET @ValF = UPPER(LEFT(@ValF, 1)) + LOWER(RIGHT(@ValF, LEN(@ValF) -1)) --Make first character a capital
END
SET @ValTemp = @ValTemp + @ValF + @LookFor --Concate to Temp Value with @LookFor
SET @ValIn = RIGHT(@ValIn, LEN(@ValIn) - @SplitAt) --Keep only the stuff after @LookFor
END
ELSE --If first charachter is non-alpha
BEGIN
SET @ValTemp = @ValTemp + LEFT(@ValIn , 1) --Concate first character to Temp Value
SET @ValIn = RIGHT(@ValIn, LEN(@ValIn) - 1) --Keep only stuff after non-alpha character
END
END
WHILE (LEFT(@ValIn , 1) NOT LIKE '[a-z]' OR LEFT(@ValIn , 1) NOT LIKE '[A-Z]') AND LEN(@ValIn) > 0 --Make sure is an Alpha Character
BEGIN
SET @ValTemp = @ValTemp + LEFT(@ValIn , 1) --Concate first character to Temp Value
IF LEN(@ValIn) = 1 --If Lenght of Data in ValIn is 1 then it was the non-alpha character
BEGIN
SET @ValIn = '' --We already concatenated to Temp Value so empty ValIn
END
ELSE
IF LEN(@ValIn) > 0 --If Lenght of Data in ValIn is greater than 0 but not 1 then we have more data
BEGIN
SET @ValIn = RIGHT(@ValIn, LEN(@ValIn) - 1) --Remove non-alpha first character from ValIn
END
END
IF LEN(@ValIn) > 0 --Make sure we still have data
BEGIN
WHILE CHARINDEX('-', @ValIn, 1) > 0 --Break up hyphenated words
BEGIN
SET @SplitAt2 = CHARINDEX('-', @ValIn, 1) --Find position of hyphen
SET @ValF = LEFT(@ValIn, @SplitAt2 - 1) --Pull off data in front of hyphen
IF LEFT(@ValF, 2) IN ('o''', 'mc') --Check for special instances
BEGIN
SET @ValF = UPPER(LEFT(@ValF, 1)) + LOWER(RIGHT(LEFT(@ValF, 2),1)) + UPPER(RIGHT(LEFT(@ValF, 3),1)) + LOWER(RIGHT(@ValF, LEN(@ValF) -3)) --Set correct case for special instances
END
ELSE --If no special instances
BEGIN
SET @ValF = UPPER(LEFT(@ValF, 1)) + LOWER(RIGHT(@ValF, LEN(@ValF) -1)) --Make first character Upper case, rest lower
END
SET @ValTemp = @ValTemp + @ValF + '-' --Concate correct case and - to Temp Value
SET @ValIn = RIGHT(@ValIn, LEN(@ValIn) - @SplitAt2) --Keep only data after hyphen
END
--After hyphens are gone
IF LEFT(@ValIn, 2) IN ('o''', 'mc') --Check for special instances
BEGIN
SET @ValL = UPPER(LEFT(@ValIn, 1)) + LOWER(RIGHT(LEFT(@ValIn, 2),1)) + UPPER(RIGHT(LEFT(@ValIn, 3),1)) + LOWER(RIGHT(@ValIn, LEN(@ValIn) -3)) --Set correct case for special instances
END
ELSE --If no special instances
BEGIN
SET @ValL = UPPER(LEFT(@ValIn, 1)) + LOWER(RIGHT(@ValIn, LEN(@ValIn) -1)) --Make first character Upper case, rest lower
END
END
---We are done send output
SET @ValOut = @ValTemp + @ValL
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply