I need to convert all caps to proper case.
I have tblDataParcel containing the column OwnerName.
I need to go from JOHN SMITH to John Smith.
I found a Function but have not been able to get it to work.
Thank you for any assistance.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[initcap] (
@string varchar(2000)
)
RETURNS varchar(2000)
AS
--Author: Scott Pletcher
--Example: SELECT dbo.initcap('aBC mike;a alkj/as12k 43')
BEGIN
DECLARE @previousByte int
DECLARE @byte int
SET @string = LOWER(@string)
IF LEFT(@string, 1) LIKE '[a-z]'
SET @string = STUFF(@string, 1, 1, UPPER(LEFT(@string, 1)))
SET @previousByte = 2
WHILE 1 = 1
BEGIN
SET @byte = PATINDEX('%[^a-z0-9][a-z]%', SUBSTRING(@string, @previousByte, 2000))
--PRINT 'Byte = ' + CAST(@byte AS varchar(10))
IF @byte = 0
BREAK
SET @string = STUFF(@string, @previousByte + @byte, 1,
UPPER(SUBSTRING(@string, @previousByte + @byte, 1)))
SET @previousByte = @previousByte + @byte + 1
--PRINT 'PrevByte = ' + CAST(@previousByte AS varchar(10))
END --WHIILE
RETURN @string
END --FUNCTION
Here is another version:
ALTER Function dbo.TitleCase(@inputString varchar(8000))
Returns varchar(8000)
As
Begin
Declare @position int
Select @inputString = stuff(lower(@inputString), 1, 1, upper(left(@inputString, 1)))
, @position = patindex('%[^a-zA-Z][a-z]%', @inputString collate Latin1_General_Bin)
While @position > 0
Select @inputString = stuff(@inputString, @position, 2, upper(substring(@inputString, @position, 2)))
, @position = patindex('%[^a-zA-Z][a-z]%', @inputString collate Latin1_General_Bin)
Return @inputString
End
GO
I also found a thing called SDU Tools which allows one to import such functions ready made.
I'm feeling really stupid - If I run these procedures in a query with the table open nothing happens. What am I doing wrong?
Can you show us what you are doing? Not sure what you mean by 'table open'...
You should have a query:
SELECT ..., dbo.TitleCase(a.column) As TitleCase
FROM yourtable a
Just want to change the all caps to proper case in OwnerName1.
create table bzoom100(OwnerName1 nvarchar(150))
insert into bzoom100
select upper(name) from sys.tables union
select N'ምድሪ' union
select upper('Geschäfts') union
select upper('Möglichkeit')
SELECT *, dbo.TitleCase(a.OwnerName1) As TitleCase
FROM bzoom100 a
odd when using collation Latin1_General_Bin
RÉDUISANT becomes RÉDuisant
but its good when using collation Latin1_General_100_CS_AS_SC
(just in case there are owner names with diacritic and all.
I am guessing that you right-clicked the table name and selected open (or edit)? You actually need to be in a new query window - click on 'New Query' in the toolbar to open that window.
Once opened - you can then write a query in that window to select the data from your table.
Select dp.RefSubID
, dp.SubIDType
, dp.ParcelID
, dp.OwnerName1
, NewOwnerName = dbo.TitleCase(dp.OwnerName1)
From dbo.tblDataParcel dp
If you want to update the data in the table and permanently change the values in the database, you have to issue an UPDATE statement. That can be done using the following:
Begin Transaction; --start an explicit transaction so we can rollback the changes and verify
--==== Show the table before the changes
Select *
From dbo.tblDataParcel dp;
--==== Update the table
Update dbo.tblDataParcel
Set OwnerName1 = dbo.TitleCase(OwnerName1);
--==== Show the table after the changes
Select *
From dbo.tblDataParcel dp;
--==== rollback or commit the changes
Rollback Transaction; --leave this uncommented so you can review and validate
--Commit Transaction; --uncomment this and comment the rollback when you want to make the changes permanent
You can execute the above in a query window - this will show you the table before the changes and after the changes. Once you are satisfied the changes are working as expected, uncomment the commit, comment out the rollback and execute. That will run the code and commit the changes to the database.
Another option would be to create a computed column for the table. You can add the computed column and use the function as the source for the column:
Alter Table dbo.tblDataParcel Add OwnerName1TitleCase As dbo.TitleCase(OwnerName1) PERSISTED;
Change the column name to whatever you want it to be - and now any time a new row is added to the table and someone enters a value in OwnerName1 it will automatically be converted and show up in your new column.
This did it - Thank you!
Glad I could help - which option worked for you?
hi
i am looking at this topic
which is from 10 months ago !!!!!
i tried to do this using
user defined function !!!!
please click arrow to the left for FUNCTION Script
-- Drop the function if it already exists
IF OBJECT_ID('dbo.InitCap') IS NOT NULL
DROP FUNCTION dbo.InitCap;
GO
-- Implementing Oracle INITCAP function
CREATE FUNCTION dbo.InitCap (@inStr VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @outStr VARCHAR(8000) = LOWER(@inStr),
@char CHAR(1),
@alphanum BIT = 0,
@len INT = LEN(@inStr),
@pos INT = 1;
-- Iterate through all characters in the input string
WHILE @pos <= @len BEGIN
-- Get the next character
SET @char = SUBSTRING(@inStr, @pos, 1);
-- If the position is first, or the previous characater is not alphanumeric
-- convert the current character to upper case
IF @pos = 1 OR @alphanum = 0
SET @outStr = STUFF(@outStr, @pos, 1, UPPER(@char));
SET @pos = @pos + 1;
-- Define if the current character is non-alphanumeric
IF ASCII(@char) <= 47 OR (ASCII(@char) BETWEEN 58 AND 64) OR
(ASCII(@char) BETWEEN 91 AND 96) OR (ASCII(@char) BETWEEN 123 AND 126)
SET @alphanum = 0;
ELSE
SET @alphanum = 1;
END
RETURN @outStr;
END
GO
please click arrow to the left for DROP Create Data
drop table #data
go
create table #data
(
name varchar(100)
)
go
insert into #data select 'JOHN SMITH'
insert into #data select 'john smith'
go
SELECT
'SQL output '
,name
,dbo.InitCap(name) as Initialized_Capital
FROM #data
go
does not work on some locales
create table #data(OwnerName1 nvarchar(150))
insert into #data
select upper(name) from sys.tables union
select N'ምድሪ' union
select upper('Geschäfts') union
select upper('Möglichkeit') union
select upper('RÉDUISANT') union
select N'मुर्गी' union
select N'කුකුල් මස්' union
select N'ચિકન'
SELECT
'SQL output '
,OwnerName1
,dbo.InitCap(OwnerName1) as Initialized_Capital
--,dbo.TitleCase(OwnerName1) As TitleCase
FROM #data
drop table #data
hi yosiasz
Looks like i did not take LOCALES into account !!!
Oops !!!
imagine if this was an UPDATE statement on a big database that contained millions of rows with non Latin words. poof goes the data with ????? and poof goes your employment
after poof goes your employment
Time to Start Selling
Lime Soda ..
And make millions of dollars
Hey, I love anything lime. Or tamarind.