SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing case of data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/25/2013 :  06:17:38  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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

India
534 Posts

Posted - 03/25/2013 :  06:58:10  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
select upper(LEFT(company,1))+''+RIGHT(company, LEN(company) - 1) as company from Account
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/25/2013 :  07:10:21  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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

India
534 Posts

Posted - 03/25/2013 :  07:23:58  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
can you share you table ddl script with data?
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/25/2013 :  07:37:28  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
How do I generate the DDL Script?

JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 03/25/2013 :  07:39:49  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

United Kingdom
199 Posts

Posted - 03/25/2013 :  07:50:56  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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

India
534 Posts

Posted - 03/25/2013 :  07:52:50  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
also send some sample data.
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/25/2013 :  07:57:24  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
Sample data:

Company
Test Ltd
test2 ltd
Test3 ltd

JT
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 03/25/2013 :  08:31:42  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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.

Edited by - ahmeds08 on 03/25/2013 08:32:28
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 03/25/2013 :  09:34:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 03/25/2013 :  09:37:04  Show Profile  Reply with Quote
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

Edited by - bandi on 03/25/2013 09:46:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/25/2013 :  12:18:26  Show Profile  Reply with Quote
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

India
534 Posts

Posted - 03/26/2013 :  01:36:47  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 03/26/2013 :  01:48:44  Show Profile  Reply with Quote
quote:
Originally posted by ahmeds08

quote:
Originally posted by bandi
--
Chandu


Thanks for the illustration.

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000