Change function collation

• iasonas

  SSC Veteran

  Points: 214

  More actions

  October 13, 2009 at 4:20 am

  #205447

  Hello,

  I am using SQL 2005.

  I have a function I need to create on a server in Sweden. When I create the function the collation defaults to Icelandic. Is there a way to change that?

  The server collation is set to Latin, every collation I can see on system databases, the database itself is all Latin. The only place the collation is set to Icelandic is on that function.

  Thank you

  This is the function:

  ALTER FUNCTION [dbo].[Split]

  (

  -- Add the parameters for the function here

  @List nvarchar(2000),

  @SplitOn nvarchar(1)

  )

  RETURNS @RtnValue TABLE

  (

  Id int identity(1,1),

  Value nvarchar(100)

  )

  AS

  BEGIN

  While (Charindex(@SplitOn,@List)>0)

  Begin

  Insert Into @RtnValue (value)

  Select

  Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

  Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

  End

  Insert Into @RtnValue (Value)

  Select Value = ltrim(rtrim(@List))

  Return

  END

• Silverfox

  SSC-Insane

  Points: 23150

  More actions

  October 13, 2009 at 4:22 am

  #1065120

  just a simple question, what is the collation for the user database that you are creating this function in

  this is from BOL (Books Online) from the CREATE FUNCTION section

  COLLATE collation_name

  Specifies the collation for the column. If not specified, the column is assigned the default collation of the database. Collation name can be either a Windows collation name or a SQL collation name. For a list of and more information about collations, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

  The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types.

  --------------------------------------------------------------------------------------
  [highlight]Recommended Articles on How to help us help you and[/highlight]
  [highlight]solve commonly asked questions[/highlight]

  Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
  Managing Transaction Logs by Gail Shaw[/url]
  How to post Performance problems by Gail Shaw[/url]
  Help, my database is corrupt. Now what? by Gail Shaw[/url]