January 4, 2011 at 5:28 am
Hi,
I have a field called contact that has peoples firstname and lastname in, the data looks like "JOHN SMITH" for exmaple. What i need to do is to alter all the rows and chnage the contact names case to to look like "John Smith" in this example.
I do have two other fields, the firstname field that holds the "JOHN" and the lastname field that holds "SMITH". I was going to use this code to change each field seperately the update the contact field with those two fields once the case was altered but i get the following error.
Any ideas would be great. Thanks for looking.
update dbo.wce_contact set firstname = UPPER(LEFT(firstname, 1)) + LOWER(SUBSTRING(firstname, 2, (LEN(fname) -1)))
Error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
January 4, 2011 at 5:46 am
sc-w (1/4/2011)
Hi,I have a field called contact that has peoples firstname and lastname in, the data looks like "JOHN SMITH" for exmaple. What i need to do is to alter all the rows and chnage the contact names case to to look like "John Smith" in this example.
I do have two other fields, the firstname field that holds the "JOHN" and the lastname field that holds "SMITH". I was going to use this code to change each field seperately the update the contact field with those two fields once the case was altered but i get the following error.
Any ideas would be great. Thanks for looking.
update dbo.wce_contact set firstname = UPPER(LEFT(firstname, 1)) + LOWER(SUBSTRING(firstname, 2, (LEN(fname) -1)))
Error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
If Firstname only holds a single name you dont need the Len(fname)-1 part. Any length >= len of the string will work. So Len(firstname) would be okay. Or you could do the size of the column say FirstName is varchar(30). Then 30 could be used.
Your propably getting the error your getting because you have a first name thats an empty string (''). So your getting a length of -1 which SubString wont accept.
/T
January 4, 2011 at 6:07 am
this is the ProperCase function I use; it's from a post by Jeff Moden, i believe, harvested long long ago.
you can search for "ProperCase" or "InitCaps" here on SSC and find some other script contributions as well:
CREATE FUNCTION ProperCase(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;with
a1 as (select 1 as N union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1),
a2 as (select 1 as N from a1 as a cross join a1 as b),
a3 as (select 1 as N from a2 as a cross join a2 as b),
a4 as (select 1 as N from a3 as a cross join a2 as b),
Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
--first char is always capitalized?
CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))
END
FROM Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')
Lowell
January 4, 2011 at 6:47 am
Thanks for both the replies.
I really like that function. How do i declare @OriginalText to be an actual field that alread exists?
Thanks again.
January 4, 2011 at 7:20 am
Here's a Title Case script I set up that avoids UDF overhead. It assumes you have a Numbers table. You can use the CTE from Jeff's script if you prefer.
Basically, you'd just use your table and column instead of #Strings.
set nocount on;
declare @Delimiters char(5);
select @Delimiters = ' -(/&';
if object_id(N'tempdb..#Strings') is null
begin
create table #Strings (
ID int identity primary key,
String varchar(1000));
insert into #Strings (String)
select ' this is a string with a hyphenated-word&ersand and numbers in it 123'
from #Numbers;
end;
select
(select
case
when Number = 1 then upper(substring(String, number, 1))
when @Delimiters like '%' + substring(String, number-1, 1) + '%' then upper(substring(String, number, 1))
else lower(substring(String, number, 1))
end
from dbo.Numbers
where Number <= len(String)
order by Number
for XML path(''), type).value('.','varchar(1000)')
from #Strings;
Edit: Forgot to mention, the @Delimiters variable/parameter allows you to specify word-breaks where you want to have a capital letter following specific punctuation. If, for example, you want "word1/word2" to end up as "Word1/Word2", you would include a "/" character in that variable.
This won't just capitalize a first letter, it will Title Case a phrase just as easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 8:12 am
sc-w (1/4/2011)
Thanks for both the replies.I really like that function. How do i declare @OriginalText to be an actual field that alread exists?
Thanks again.
I should have added that to my example;
it's just a simple update statement:
update dbo.wce_contact set firstname = dbo.ProperCase(firstname )
Lowell
January 4, 2011 at 9:38 am
GSquared (1/4/2011)
Here's a Title Case script I set up that avoids UDF overhead. It assumes you have a Numbers table. You can use the CTE from Jeff's script if you prefer.Basically, you'd just use your table and column instead of #Strings.
set nocount on;declare @Delimiters char(5);
select @Delimiters = ' -(/&';
if object_id(N'tempdb..#Strings') is null
begin
create table #Strings (
ID int identity primary key,
String varchar(1000));
insert into #Strings (String)
select ' this is a string with a hyphenated-word&ersand and numbers in it 123'
from #Numbers;
end;
select
(select
case
when Number = 1 then upper(substring(String, number, 1))
when @Delimiters like '%' + substring(String, number-1, 1) + '%' then upper(substring(String, number, 1))
else lower(substring(String, number, 1))
end
from dbo.Numbers
where Number <= len(String)
order by Number
for XML path(''), type).value('.','varchar(1000)')
from #Strings;
Edit: Forgot to mention, the @Delimiters variable/parameter allows you to specify word-breaks where you want to have a capital letter following specific punctuation. If, for example, you want "word1/word2" to end up as "Word1/Word2", you would include a "/" character in that variable.
This won't just capitalize a first letter, it will Title Case a phrase just as easily.
That's a great extra tool to have. But strictly speaking about performance, your inline code is 2 times slower than Jeff's function.
Also here's a modified version of jeff's function that goes ± 15% faster than the original over 5000 rows in the syscomments table.
ALTER FUNCTION dbo.ProperCase_Ninja(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;with
a1 as (select 1 as N union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1),
a2 as (select 1 as N from a1 as a cross join a1 as b),
a3 as (select 1 as N from a2 as a cross join a2 as b),
a4 as (select 1 as N from a3 as a cross join a2 as b),
Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
-- --first char is always capitalized?
--CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
-- WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
-- ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))
--END
--
--maximize short circuit efficiency but putting the cases in the best guess occurance order
CASE WHEN SUBSTRING(@OriginalText,Tally.N -1,1) <> ' ' THEN LOWER(SUBSTRING(@OriginalText,Tally.N,1))
WHEN Tally.N = 1THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
ELSE UPPER(SUBSTRING(@OriginalText,Tally.N,1))
END
FROM Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
DECLARE @Bitbucket VARCHAR(8000)
select @Bitbucket = dbo.ProperCase(text) FROM sys.syscomments
GO
DECLARE @Bitbucket VARCHAR(8000)
select @Bitbucket = dbo.ProperCase_Ninja(text) FROM sys.syscomments
-- ± 15% faster
GO
January 4, 2011 at 9:40 am
Yes, it's slower. That's because of the extra checks for word-breaks on variable delimiters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 9:42 am
So much help now my head is spinning! Thanks
How do i declare @OriginalText to be an actual field that alread exists?
Thanks again.
January 4, 2011 at 9:47 am
Just put the column name in there instead of the variable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 9:52 am
GSquared (1/4/2011)
Just put the column name in there instead of the variable.
Like this (note that my numbers table is called tally)
set nocount on;
declare @Delimiters char(5);
select @Delimiters = ' -(/&';
select
(select
case
when N = 1 then upper(substring(text, N, 1))
when @Delimiters like '%' + substring(text, N-1, 1) + '%' then upper(substring(text, N, 1))
else lower(substring(text, N, 1))
end
from dbo.Tally
where N <= len(text)
order by N
for XML path(''), type).value('.','varchar(8000)')
from sys.syscomments;
January 4, 2011 at 9:59 am
I did try replacing all the instances of @OriginalText and put in the field name contact but i get the following errors:
CREATE FUNCTION ProperCase(contact)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;with
a1 as (select 1 as N union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1),
a2 as (select 1 as N from a1 as a cross join a1 as b),
a3 as (select 1 as N from a2 as a cross join a2 as b),
a4 as (select 1 as N from a3 as a cross join a2 as b),
Tally as (select top (len(contact)) row_number() over (order by N) as N from a4)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
--first char is always capitalized?
CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(contact,Tally.N,1))
WHEN SUBSTRING(contact,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(contact,Tally.N,1))
ELSE LOWER(SUBSTRING(contact,Tally.N,1))
END
FROM Tally WHERE Tally.N <= LEN(contact)
RETURN @CleanedText
END
GO
select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')
Error:
Msg 102, Level 15, State 1, Procedure ProperCase, Line 1
Incorrect syntax near 'contact'.
Msg 178, Level 15, State 1, Procedure ProperCase, Line 25
A RETURN statement with a return value cannot be used in this context.
(1 row(s) affected)
January 4, 2011 at 10:00 am
Ah. No, don't modify the function. Take your original query, and use the function instead of your Upper/Lower pieces.
Lowell posted an example a few posts back. Take a look at his post.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 11:11 am
You know me... I'll always be one of the first to come up with a Tally Table solution and (now) to avoid scalar UDF's like the plague. However, I've always said the Tally Table isn't a panacea and not all rules about avoiding scalar UDF's are absolutely cut'n'dry.
I believe you'll find the following scalar function is about twice as fast as a Tally Table solution. Yeah... me too... I was shocked. 😀
CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))
/***************************************************************************************************
Purpose:
Capitalize any lower case alpha character which follows any non alpha character or single quote.
Revision History:
Rev 00 - 24 Feb 2010 - George Mastros - Initial concept
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function
Rev 01 - 25 Sep 2010 - Jeff Moden
- Redaction for personal use and added documentation.
- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered
- and the reduction of multiple SET statements to just 2 SELECT statements.
- Add no-cap single-quote by single-quote to the filter.
***************************************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
----------------------------------------------------------------------------------------------------
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
-- need to update. The collation here is essential to making this so simple.
-- A-z is equivalent to the slower A-Z
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
-- Note the reason for the speed here is that ONLY places that need capitalization
-- are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
----------------------------------------------------------------------------------------------------
RETURN @String;
END ;
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2011 at 11:28 am
Jeff Moden (1/4/2011)
You know me... I'll always be one of the first to come up with a Tally Table solution and (now) to avoid scalar UDF's like the plague. However, I've always said the Tally Table isn't a panacea and not all rules about avoiding scalar UDF's are absolutely cut'n'dry.I believe you'll find the following scalar function is about twice as fast as a Tally Table solution. Yeah... me too... I was shocked. 😀
CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))/***************************************************************************************************
Purpose:
Capitalize any lower case alpha character which follows any non alpha character or single quote.
Revision History:
Rev 00 - 24 Feb 2010 - George Mastros - Initial concept
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function
Rev 01 - 25 Sep 2010 - Jeff Moden
- Redaction for personal use and added documentation.
- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered
- and the reduction of multiple SET statements to just 2 SELECT statements.
- Add no-cap single-quote by single-quote to the filter.
***************************************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
----------------------------------------------------------------------------------------------------
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
-- need to update. The collation here is essential to making this so simple.
-- A-z is equivalent to the slower A-Z
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
-- Note the reason for the speed here is that ONLY places that need capitalization
-- are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
----------------------------------------------------------------------------------------------------
RETURN @String;
END ;
Looks like my test data is really different from yours. I'm still using syscomments and your function seems to be at the very least 10 times slower than the tally version. I have ± 4500 rows in syscomments and I can't go past 300 before running out of time to beat the tally versions.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply