Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing case of data

Author  Topic 

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-25 : 06:17:38
I have an 'Account' table with 'Company' field containing information that isn't first letter capitalised. Example below


Company
Test Ltd
test2 ltd
Test3 ltd


I would like to know if it's possible to write a query that would capitalise the first letter of each word within the 'company' field? Eg.

Company
Test Ltd
Test2 Ltd
Test3 Ltd

Thanks,



JT

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-25 : 06:58:10
select upper(LEFT(company,1))+''+RIGHT(company, LEN(company) - 1) as company from Account
Go to Top of Page

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-25 : 07:10:21
Msg 536, Level 16, State 2, Line 1
Invalid length parameter passed to the RIGHT function.

Any ideas?

JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-25 : 07:23:58
can you share you table ddl script with data?
Go to Top of Page

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-25 : 07:37:28
How do I generate the DDL Script?

JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-25 : 07:39:49
1.Rt click on the table name-->script table as-->Create to-->new query editor window
Go to Top of Page

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-25 : 07:50:56
USE [saleslogix]
GO

/****** Object: Table [sysdba].[ACCOUNT] Script Date: 03/25/2013 11:44:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [sysdba].[ACCOUNT](
[ACCOUNTID] [char](12) NOT NULL,
[TYPE] [varchar](64) NULL,
[COMPANY] [varchar](128) NULL,
[DIVISION] [varchar](64) NULL,
[SICCODE] [varchar](64) NULL,
[PARENTID] [char](12) NULL,
[DESCRIPTION] [varchar](128) NULL,
[ADDRESSID] [char](12) NULL,
[SHIPPINGID] [char](12) NULL,
[REGION] [varchar](64) NULL,
[MAINPHONE] [varchar](32) NULL,
[ALTERNATEPHONE] [varchar](32) NULL,
[FAX] [varchar](32) NULL,
[TOLLFREE] [varchar](32) NULL,
[TOLLFREE2] [varchar](32) NULL,
[OTHERPHONE1] [varchar](32) NULL,
[OTHERPHONE2] [varchar](32) NULL,
[OTHERPHONE3] [varchar](32) NULL,
[EMAIL] [varchar](128) NULL,
[EMAILTYPE] [varchar](64) NULL,
[WEBADDRESS] [varchar](128) NULL,
[SECCODEID] [char](12) NOT NULL,
[REVENUE] [decimal](17, 4) NULL,
[EMPLOYEES] [int] NULL,
[INDUSTRY] [varchar](64) NULL,
[CREDITRATING] [varchar](10) NULL,
[NOTES] [text] NULL,
[STATUS] [varchar](64) NULL,
[ACCOUNTMANAGERID] [char](12) NULL,
[REGIONALMANAGERID] [char](12) NULL,
[DIVISIONALMANAGERID] [char](12) NULL,
[NATIONALACCOUNT] [char](1) NULL,
[TARGETACCOUNT] [char](1) NULL,
[TERRITORY] [varchar](64) NULL,
[CREATEUSER] [char](12) NULL,
[MODIFYUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYDATE] [datetime] NULL,
[ACCOUNT_UC] [varchar](128) NULL,
[AKA] [varchar](64) NULL,
[CURRENCYCODE] [varchar](64) NULL,
[INTERNALACCOUNTNO] [varchar](32) NULL,
[EXTERNALACCOUNTNO] [varchar](32) NULL,
[PARENTACCOUNTNO] [varchar](32) NULL,
[ALTERNATEKEYPREFIX] [varchar](8) NULL,
[ALTERNATEKEYSUFFIX] [varchar](24) NULL,
[DEFAULTTICKETSECCODEID] [char](12) NULL,
[NOTIFYDEFECTS] [char](1) NULL,
[NOTIFYONCLOSE] [char](1) NULL,
[NOTIFYONSTATUS] [char](1) NULL,
[SHORTNOTES] [varchar](255) NULL,
[USERFIELD1] [varchar](80) NULL,
[USERFIELD2] [varchar](80) NULL,
[USERFIELD3] [varchar](80) NULL,
[USERFIELD4] [varchar](80) NULL,
[USERFIELD5] [varchar](80) NULL,
[USERFIELD6] [varchar](80) NULL,
[USERFIELD7] [varchar](80) NULL,
[USERFIELD8] [varchar](80) NULL,
[USERFIELD9] [varchar](80) NULL,
[USERFIELD10] [varchar](80) NULL,
[CAMPAIGNID] [char](12) NULL,
[DONOTSOLICIT] [char](1) NULL,
[SCORE] [varchar](32) NULL,
[TICKER] [varchar](16) NULL,
[SUBTYPE] [varchar](64) NULL,
[LEADSOURCEID] [char](12) NULL,
[IMPORTSOURCE] [varchar](24) NULL,
[ENGINEERID] [char](12) NULL,
[SALESENGINEERID] [char](12) NULL,
[RELATIONSHIP] [int] NULL,
[LASTHISTORYBY] [char](12) NULL,
[LASTHISTORYDATE] [datetime] NULL,
[BUSINESSDESCRIPTION] [varchar](2000) NULL,
[WEBADDRESS2] [varchar](128) NULL,
[WEBADDRESS3] [varchar](128) NULL,
[WEBADDRESS4] [varchar](128) NULL,
[GLOBALSYNCID] [varchar](36) NULL,
[APPID] [varchar](12) NULL,
[TICK] [int] NULL,
[LASTERPSYNCUPDATE] [datetime] NULL,
[PRIMARYOPERATINGCOMPID] [varchar](12) NULL,
[PROMOTEDTOACCOUNTING] [varchar](1) NULL,
[CREATESOURCE] [varchar](50) NULL,
[SICDESC] [varchar](254) NULL,
CONSTRAINT [ACCOUNT_ACCOUNTID_PK] PRIMARY KEY NONCLUSTERED
(
[ACCOUNTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-25 : 07:52:50
also send some sample data.
Go to Top of Page

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-25 : 07:57:24
Sample data:

Company
Test Ltd
test2 ltd
Test3 ltd

JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-25 : 08:31:42
same query works perfect to me.
select upper(LEFT(company,1))+''+RIGHT(company, LEN(company) -1) as company from account.
Dont know what's the issue with your machine.

Iam Unable to upload the screen shot.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 09:34:08
quote:
Originally posted by ahmeds08

same query works perfect to me.
select upper(LEFT(company,1))+''+RIGHT(company, LEN(company) -1) as company from account.
Dont know what's the issue with your machine.
Iam Unable to upload the screen shot.


If OP had the empty string in company column, then that error will occur
See the following illustration
DECLARE @InitCapTab TABLE(Company VARCHAR(20))
INSERT INTO @InitCapTab VALUES('Test Ltd'), ('test2 ltd'), ('Test3 ltd'), ('')
--ahmeds08's Solution...
SELECT upper(LEFT(company,1))+''+RIGHT(company, LEN(company) -1) FROM @InitCapTab

Your solution just capitalise first character in the Company column, but not the first character in each word of company column

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 09:37:04
quote:
Originally posted by Topaz

Sample data:
Company
Test Ltd
test2 ltd
Test3 ltd
JT

Hi Topaz,

Will the Company column has only two words or more?
Is there possibility to have more than two words in Company column?

If Yes, then follow this link ( You can get solution)
http://beyondrelational.com/modules/2/blogs/70/posts/10901/tsql-initcap-function-convert-a-string-to-proper-case.aspx
--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-25 : 12:18:26
why do you want to do this in DB? SQL Server by default is case insensitive so there's no need to do it at db level. You can always do these type of changes for display purpose at front end application.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-26 : 01:36:47
quote:
Originally posted by bandi

quote:
Originally posted by ahmeds08

same query works perfect to me.
select upper(LEFT(company,1))+''+RIGHT(company, LEN(company) -1) as company from account.
Dont know what's the issue with your machine.
Iam Unable to upload the screen shot.


If OP had the empty string in company column, then that error will occur
See the following illustration
DECLARE @InitCapTab TABLE(Company VARCHAR(20))
INSERT INTO @InitCapTab VALUES('Test Ltd'), ('test2 ltd'), ('Test3 ltd'), ('')
--ahmeds08's Solution...
SELECT upper(LEFT(company,1))+''+RIGHT(company, LEN(company) -1) FROM @InitCapTab

Your solution just capitalise first character in the Company column, but not the first character in each word of company column

--
Chandu



Thanks for the illustration.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 01:48:44
quote:
Originally posted by ahmeds08

quote:
Originally posted by bandi
--
Chandu


Thanks for the illustration.

Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -