November 13, 2014 at 4:31 am
Any advice appreciated please.
In many of our reports, some text comes through in upper case, eg a name as:
JOHN SMITH
Or in lower case - john smith
Is there any code / an expression we can use in our queries to allow regular capitalisation, so the data always appears (in a report) as:
John Smith ?
November 13, 2014 at 5:35 am
Give this a try
create function dbo.ProperCase (@name varchar(70))
returns varchar(70)
as
begin
declare @output varchar(70)
declare @upper varchar(70)
declare@space int
declare @first varchar(70)
declare @Last varchar(70);
set @upper= rtrim(ltrim(upper(@name)))
set @space= patindex('% %',@upper)
set @first= left(@upper,@space)
set @Last= reverse(left(reverse(@upper),@space) )
set @output =
left(@first,1)+lower(right(@first,len(@first)))
+left(@last,1)+lower(right(@last,len(@last)-1))
return @output;
end
go
select dbo.ProperCase (' JOHN SMITH ')
EDIT To handle superfluous spaces
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 13, 2014 at 9:00 am
This is a common topic and the functions have pros and cons.
Here's a function by Jeff Moden that will run amazingly fast despite being a scalar function.
CREATE FUNCTION [dbo].[InitialCap](@String [varchar](8000))
RETURNS [varchar](8000) WITH EXECUTE AS CALLER
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 ;
And here's a thread with multiple solutions and their test cases.
http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspx
November 13, 2014 at 9:39 am
Thanks both for getting back. However your answers presume a technical knowledge that I do not currently possess!
I have a field that is bringing through a business name into my report as, for example: GREAT CLOTHES
I am looking for assistance with an expression, using UCase and LCase if possible, that will help express this as :
Great Clothes
Any suggestions appreciated - thanks.
November 13, 2014 at 9:55 am
We're giving you the code for the user defined functions(UDFs). There's no system function that will complete the problem easily and correctly.
The code in the functions might be complex but you can get time to study it, go through every function to know what it does and understand each statement.
You can use scalar UDFs as normal system scalar functions. For table valued functions (mentioned in the thread I linked to) you need to use CROSS APPLY. You can find examples in that thread in the posts that have the performance tests.
November 13, 2014 at 10:03 am
Presuming from your other post you're looking for a VB answer:
=StrConv(Fields!FieldName.Value,vbProperCase)
Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL 🙂
November 13, 2014 at 10:48 am
Thanks Gazareth, that solution worked perfectly. Also thanks for the tip re the correct forum.
November 13, 2014 at 11:21 am
Gazareth (11/13/2014)
Presuming from your other post you're looking for a VB answer:=StrConv(Fields!FieldName.Value,vbProperCase)
Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL 🙂
Nice catch, I didn't realize that this was meant for SSRS. Knowing that, I might not have even tried to answer because my knowledge is very limited (I know how to spell it :hehe:).
November 13, 2014 at 11:31 am
Just for quick fun, here is an alternative approach, should be easy to turn it into an iTVF (SQL Server 2012 and later)
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @CAP_TEXT VARCHAR(50) = 'UPPER CASE STRING AND a lower case string';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@CAP_TEXT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5)
,WORD_SET AS
(
SELECT
NM.N
,SUBSTRING(@CAP_TEXT,NM.N,1) AS T_CHAR
,SUM(CASE WHEN ASCII(SUBSTRING(@CAP_TEXT,NM.N,1)) < 65 THEN 1 ELSE 0 END) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY NM.N
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GRP_NO
FROM NUMS NM
)
SELECT
(
SELECT
CASE WHEN ROW_NUMBER() OVER
(
PARTITION BY WS.GRP_NO
ORDER BY WS.N
) - SIGN(WS.GRP_NO) = 1 THEN UPPER(WS.T_CHAR)
ELSE LOWER(WS.T_CHAR)
END
FROM WORD_SET WS
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(200)') AS PROPER_CASE
;
Results
PROPER_CASE
------------------------------------------
Upper Case String And A Lower Case String
Edit: added 2012 and later
November 14, 2014 at 2:14 am
faulknerwilliam2 (11/13/2014)
Thanks Gazareth, that solution worked perfectly. Also thanks for the tip re the correct forum.
No problem. Of course, if you're interested in learning T-SQL, this is definitely the right place 🙂
November 14, 2014 at 2:15 am
Luis Cazares (11/13/2014)
Gazareth (11/13/2014)
Presuming from your other post you're looking for a VB answer:=StrConv(Fields!FieldName.Value,vbProperCase)
Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL 🙂
Nice catch, I didn't realize that this was meant for SSRS. Knowing that, I might not have even tried to answer because my knowledge is very limited (I know how to spell it :hehe:).
Ha, I'd answered another couple of posts by the OP, so knew it was likely to be SSRS.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply