Oracle's INTICAP in sql

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  August 5, 2014 at 7:57 am

  #291683

  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 ???

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  August 5, 2014 at 8:19 am

  #1735213

  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/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 5, 2014 at 8:19 am

  #1735214

  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.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  August 5, 2014 at 8:20 am

  #1735215

  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/

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  August 5, 2014 at 8:21 am

  #1735216

  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/

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  August 5, 2014 at 8:22 am

  #1735218

  Luis Cazares (8/5/2014)

  ---

  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.

  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/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 5, 2014 at 8:39 am

  #1735232

  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.aspx

  There'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

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 5, 2014 at 8:46 am

  #1735235

  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

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2