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.
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 belowCompanyTest Ltdtest2 ltdTest3 ltdI 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.CompanyTest LtdTest2 LtdTest3 LtdThanks,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 |
|
|
Topaz
Posting Yak Master
199 Posts |
Posted - 2013-03-25 : 07:10:21
|
Msg 536, Level 16, State 2, Line 1Invalid length parameter passed to the RIGHT function.Any ideas?JT |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-25 : 07:23:58
|
can you share you table ddl script with data? |
|
|
Topaz
Posting Yak Master
199 Posts |
Posted - 2013-03-25 : 07:37:28
|
How do I generate the DDL Script?JT |
|
|
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 |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOJT |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-25 : 07:52:50
|
also send some sample data. |
|
|
Topaz
Posting Yak Master
199 Posts |
Posted - 2013-03-25 : 07:57:24
|
Sample data:CompanyTest Ltdtest2 ltdTest3 ltdJT |
|
|
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. |
|
|
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 occurSee 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 @InitCapTabYour solution just capitalise first character in the Company column, but not the first character in each word of company column--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 occurSee 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 @InitCapTabYour 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. |
|
|
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 |
|
|
|
|
|
|
|