Mr_Shaw
asked on
Convert lower case to uppercase after a full stop/period
I have a free text column which has be populated with sentences that don't properly use upper and lower case letters.
For example, lower case letters after full stops/period.
I therefore need to cleanse the text to make sure the correct case is being used.
Is this possible.....
I am using SQL 2005.
Thanks
For example, lower case letters after full stops/period.
I therefore need to cleanse the text to make sure the correct case is being used.
Is this possible.....
I am using SQL 2005.
Thanks
Please convert your desire character to ascii and code as require and again convert it to char.
select ASCII('A')
select ASCII('a')
select ASCII('A')
select ASCII('a')
Hi Mr. Shaw,
This may help you...
http://www.sqlservercentral.com/Forums/Topic910545-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic502176-338-1.aspx#bm502684
This may help you...
http://www.sqlservercentral.com/Forums/Topic910545-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic502176-338-1.aspx#bm502684
ASKER
Hi PatelAlpesh,
Once I convert the letter to ASCII what do I do?
Once I convert the letter to ASCII what do I do?
Hi,
Try this code
DECLARE @val varchar(40)
DECLARE @len int, @i int
DECLARE @res varchar(40)
SET @res = ''
SET @val = 'kjh sdkjh fhg.kjhf kj9isf. kjhhj lkjk'
SET @len = LEN(@val)
SET @i=1
WHILE(@i <= @len)
BEGIN
IF @i=1
BEGIN
SET @res = @res + UPPER(SUBSTRING(@val,@i,1) )
print(@res)
END
ELSE
BEGIN
IF SUBSTRING(@val,@i,1) = '.' AND SUBSTRING(@val,@i+1,1) <> ' '
BEGIN
SET @res = @res + SUBSTRING(@val,@i,1) + UPPER(SUBSTRING(@val, @i+1,1))
END
ELSE IF SUBSTRING(@val,@i,1) = '.' AND SUBSTRING(@val,@i+1,1) = ' '
BEGIN
SET @res = @res + SUBSTRING(@val,@i,2) + UPPER(SUBSTRING(@val, @i+2,1))
END
ELSE
BEGIN
SET @res = @res + SUBSTRING(@val,@i,1)
END
END
SET @i=@i+1
END
PRINT(@res)
Try this code
DECLARE @val varchar(40)
DECLARE @len int, @i int
DECLARE @res varchar(40)
SET @res = ''
SET @val = 'kjh sdkjh fhg.kjhf kj9isf. kjhhj lkjk'
SET @len = LEN(@val)
SET @i=1
WHILE(@i <= @len)
BEGIN
IF @i=1
BEGIN
SET @res = @res + UPPER(SUBSTRING(@val,@i,1)
print(@res)
END
ELSE
BEGIN
IF SUBSTRING(@val,@i,1) = '.' AND SUBSTRING(@val,@i+1,1) <> ' '
BEGIN
SET @res = @res + SUBSTRING(@val,@i,1) + UPPER(SUBSTRING(@val, @i+1,1))
END
ELSE IF SUBSTRING(@val,@i,1) = '.' AND SUBSTRING(@val,@i+1,1) = ' '
BEGIN
SET @res = @res + SUBSTRING(@val,@i,2) + UPPER(SUBSTRING(@val, @i+2,1))
END
ELSE
BEGIN
SET @res = @res + SUBSTRING(@val,@i,1)
END
END
SET @i=@i+1
END
PRINT(@res)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bing.. thanks
here is a starter function for you...
but yoiu may be better off trying to pass the text thru some sort of word processor
with grammatical correction enabled...
but yoiu may be better off trying to pass the text thru some sort of word processor
with grammatical correction enabled...
create function dbo.CorrectSentence
( @input as varchar(max)
)
returns varchar(max)
as
begin
declare @i int
declare @res varchar(max),@lookforchar char(1)
select @i=2,@res=ltrim(@input)
set @res=stuff(@res,1,1,upper(substring(@res,1,1)))
while @i<=len(@res)
begin
if substring(@res,@i,1)='.'
begin
select @i=@i+1,@lookforchar='Y'
while @lookforchar='Y'
begin
if substring(@res,@i,1) <>' '
begin
select @res=stuff(@res,@i,1,upper(substring(@res,@i,1)))
,@lookforchar='N'
end
else set @i=@i+1
end
end
Set @i=@i+1
End
Return @res
end
go
print dbo.correctsentence('a big idea. hi')
You need to create a function and call from select statement
SELECT dbo.ConvertToProperCase(co