August 11, 2005 at 4:01 am
Hello,
I am importing data from anther system where the company name is in uppercase.
I would like to format the company name better.
for example:
Company name :
Current:
JOE BLOGGS
New:
Joe Bloggs
--------------
Current:
MCPERSON
New:
McPerson
-------------
Current:
A B C ENTERPRISES
New:
A B C Enterprises
-----------------
CURRENT:
THE MOBILE COMPANY
NEW:
The Mobile Company
What string manipluation techiniques can I use to get the above results?
Thanks in advance.
August 11, 2005 at 4:26 am
DECLARE
@i INT,
@C CHAR(1),
@result VARCHAR(8000),
@formatString varchar(100)
SET @formatString = 'THE MOBILE COMPANY'
SET @result = LOWER(@formatString)
SET @i = 2
SET @result = STUFF(@result,1,1,UPPER(SUBSTRING(@formatString,1,1)))
WHILE @i<= LEN(@formatString)
BEGIN
SET @C = SUBSTRING(@formatString,@i,1)
IF (@c = ' ') OR (@c = ';') OR (@c = ':') OR (@c = '!') OR (@c = '?') OR (@c = ',')OR (@c = '.')OR (@c = '_' )
IF @i< LEN(@formatString)
BEGIN
SET @i = @i+1
SET @result = STUFF(@result,@i,1,UPPER(SUBSTRING(@formatString,@i,1)))
END
SET @i = @i+1
END
PRINT @Result
/**A strong positive mental attitude will create more miracles than any wonder drug**/
August 11, 2005 at 5:20 am
Thanks.
How can I assign a column name to the variable?
I have tried but keep getting a message saying 'Invalid column name'?
Thanks
August 11, 2005 at 6:05 am
Hey Sush!
I thought that you can do the rest...ok
4 u
--Create function Initcap
CREATE FUNCTION INITCAP (@formatString VARCHAR(8000) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@i INT,
@c CHAR(1),
@result VARCHAR(8000),
@formatString varchar(100)
SET @formatString = 'TATA CONSULTANCY SERVICES LTD.'
SET @result = LOWER(@formatString)
SET @i = 2
SET @result = STUFF(@result,1,1,UPPER(SUBSTRING(@formatString,1,1)))
WHILE @i<= LEN(@formatString)
BEGIN
SET @C = SUBSTRING(@formatString,@i,1)
IF (@c = ' ') OR (@c = ';') OR (@c = ':') OR (@c = '!') OR (@c = '?') OR (@c = ',')OR (@c = '.')OR (@c = '_' )
IF @i< LEN(@formatString)
BEGIN
SET @i = @i+1
SET @result = STUFF(@result,@i,1,UPPER(SUBSTRING(@formatString,@i,1)))
END
SET @i = @i+1
END
RETURN @result
END
--now pass the column in the function using select cmd....
SELECT dbo.INITCAP([Name of the column]) from Table
Cheers,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
August 11, 2005 at 6:07 am
Hey Sush!
I thought that you can do the rest...ok
4 u
--Create function Initcap
CREATE FUNCTION INITCAP (@formatString VARCHAR(8000) )
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@i INT,
@c CHAR(1),
@result VARCHAR(8000)
SET @result = LOWER(@formatString)
SET @i = 2
SET @result = STUFF(@result,1,1,UPPER(SUBSTRING(@formatString,1,1)))
WHILE @i<= LEN(@formatString)
BEGIN
SET @C = SUBSTRING(@formatString,@i,1)
IF (@c = ' ') OR (@c = ';') OR (@c = ':') OR (@c = '!') OR (@c = '?') OR (@c = ',')OR (@c = '.')OR (@c = '_' )
IF @i< LEN(@formatString)
BEGIN
SET @i = @i+1
SET @result = STUFF(@result,@i,1,UPPER(SUBSTRING(@formatString,@i,1)))
END
SET @i = @i+1
END
RETURN @result
END
--now pass the column in the function using select...
SELECT dbo.INITCAP([Name of the column]) from Table
Cheers,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply