August 5, 2014 at 7:57 am
Hi There,
To improve my self I just trying to solve some challenges(Oracle's INTICAP in sql). So far I just come up to below level
ie., which position to replace with capital letter.
SELECT TOP 1000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
declare @t table (id int identity,data varchar(100))
insert into @t(data)
select ' welcome to TSQL Challenge' union all
select ' this is heavenguy'
selectdata,ID,upper(substring(data,n+1,1))caps,N+1 IndextoReplace,n,SUBSTRING (DATA ,N,1)whitespace
from@t TMP,
(SELECT *
FROM Tally
WHERE N < = (SELECT MAX(LEN(DATA) )FROM @t )
)T
WHERE SUBSTRING (DATA ,N,1) = ' '
and n <=LEN(data)
i stuck up in replacing the Char from the above output. By iterating the loop we could achieve this but I want solutions other then looping
Any Ideas ???
August 5, 2014 at 8:19 am
This topic comes up around here from time to time. I have 3 different version stashed in my snippets. The first one here is from ColdCoffee.
--From Cold Coffee
DECLARE @Table TABLE
(
iD INT
,AttrVal1 VARCHAR(100)
,AttrVal2 VARCHAR(100)
)
INSERT INTO @Table
SELECT 1 , 'eXEC' ,'exec Proc myproc'
UNION ALL SELECT 2 , 'Sql' ,'Exec Proc Myproc2'
UNION ALL SELECT 3 , 'tsql' ,'CREATE FUNCTION A'
UNION ALL SELECT 4 , 'BCP' ,'bulk upload all files'
UNION ALL SELECT 5 , ' xp_cmdshell ' ,' dir /*'
; WITH CTE AS
(
SELECT T.iD
, T.AttrVal1
, T.AttrVal2
, UprCaseAttVal1 = STUFF(CrsApp1.AttVal1 ,1,1,UPPER(LEFT(CrsApp1.AttVal1,1)))
, UprCaseAttVal2 = STUFF(CrsApp2.Item ,1,1,UPPER(LEFT(CrsApp2.Item,1)))
, CrsApp2.ItemNumber
FROM @Table T
CROSS APPLY (SELECT RTRIM(LTRIM(T.AttrVal1)) , RTRIM(LTRIM(T.AttrVal2)) ) CrsApp1 (AttVal1,AttVal2)
CROSS APPLY dbo.DelimitedSplit8K (CrsApp1.AttVal2,' ') CrsApp2
)
SELECT C.iD
,AttrVal1 = C.UprCaseAttVal1
,AttrVal2 = STUFF ( ( SELECT ' '+C1.UprCaseAttVal2
FROM CTE C1
WHERE c1.iD = C.id
ORDER BY C1.ItemNumber
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))
FROM CTE C
GROUP BY C.iD , C.UprCaseAttVal1
This one makes use of the DelimitedSplit8K function. You can find that function by following the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 8:19 am
Maybe this thread can give you some ideas. http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspx
There's another option that uses a scalar function which seems to be faster but I'll have to find it.
August 5, 2014 at 8:20 am
Here is a scalar function I wrote years ago. This works pretty well but the performance is not great.
ALTER function [dbo].[InitCap]
(
@inString varchar(max)
)
returns varchar(max)
as
begin
declare @currIndex int,
@nextIndex int,
@result varchar(max),
@StrLength int
set @StrLength = len(@inString)
set @result = lower(@inString)
set @currIndex = 2
set @result = stuff(@result, 1, 1, upper(substring(@inString, 1, 1)))
while @currIndex < @StrLength begin
if (substring(@inString, @currIndex, 1) = ' ') -- you can add more delimiters here
begin
set @nextIndex = @currIndex
while 1 = 1
begin
if ascii(upper(substring(@inString, @nextIndex, 1))) between 65 and 90
begin
set @result = stuff(@result, @nextIndex ,1, upper(substring(@inString, @nextIndex, 1)))
break
end
set @nextIndex = @nextIndex+ 1
end
set @currIndex = @nextIndex
end
set @currIndex = @currIndex + 1
end
return @Result
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 8:21 am
Here is another one...unfortunately I did not record who provided this. If anybody recognizes this please let me know so I can give proper credit.
ALTER function [dbo].[NewInitCap]
(
@word varchar(2000)
) returns table
return
select camelcase = replace( word, ' ', ' ' )
from (
select case when N = 1 or substring( @word, N-1, 1 ) IN (' ', '-', '.') then upper( substring( @word, N, 1 ) )
else lower(substring( @word, N, 1 ) )
end as [text()]
from cteTally
where N <= len( @word )
for xml path( '' )
) as t( word )
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 8:22 am
Luis Cazares (8/5/2014)
Maybe this thread can give you some ideas. http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspxThere's another option that uses a scalar function which seems to be faster but I'll have to find it.
Awesome. I was thinking about this thread but wasn't sure how to find it. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 8:39 am
Sean Lange (8/5/2014)
Luis Cazares (8/5/2014)
Maybe this thread can give you some ideas. http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspxThere's another option that uses a scalar function which seems to be faster but I'll have to find it.
Awesome. I was thinking about this thread but wasn't sure how to find it. 😀
That's why it is stored in my briefcase 😀
August 5, 2014 at 8:46 am
I'm not sure, but I believe that this is the fast scalar function I was talking about. http://www.sqlservercentral.com/Forums/FindPost1222341.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply