Characters only from A to Z problem

• creliuc ciprian

  SSC Veteran

  Points: 248

  More actions

  January 31, 2006 at 4:27 am

  #96575

  I have the following query:

  set language romanian

  select distinct datename(w,t.data),r.data

  from table t

  where t.data between '20060101' and '20060107'

  that returns :

  duminică

  luni

  marţi

  miercuri

  joi

  vineri

  sîmbătă

  I need my results to be composed only from characters from 'a' to 'z'  so  that

  in the results above  ă must be 'a' , ţ must be 't' and 'î' must be 'i'

  If i convert the results to a varchar data type (convert(varchar,datename(w,t.data))) , my problem seems to be 2/3 solved as it returns following:

  duminica

  luni

  marti

  miercuri

  joi

  vineri

  sîmbata

  Now, how about that 'î' from 'sîmbata, how can he become 'i' ?

  Is there a function that converts a character set into another?

  I need a simple solution that do not involves :

  • changing any of SQL server settings

  or

  • any kind of function that searches for a specified character in a string and replace it with another

  I know it must be a simple solution , but I just couldn't find it.

  Thank You !

  a

  

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 31, 2006 at 7:58 am

  #617934

  I think that what you need is a 'destructive' collation that won't preserve accents and other 'fancy' notations, but just convert to the 'base charachter'... So I just tried a few on random, and this seems to do what you want..

  set language romanian

  select  distinct datename(w,t.data),

          (convert(varchar,datename(w,t.data)))  collate SQL_Latin1_General_CP1253_CI_AI

  from t

  where t.data between '20060101' and '20060107'

  ------------------------------ ------------------------------

  duminica                       duminica

  joi                            joi

  luni                           luni

  marti                          marti

  miercuri                       miercuri

  sîmbata                        simbata

  vineri                         vineri

  (7 row(s) affected)

  /Kenneth

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  January 31, 2006 at 9:00 pm

  #618037

  CREATE TABLE dbo.CharReplace (

     CharToReplace nchar(1) NOT NULL,

     CharReplacement nchar(1) NOT NULL

    )

  INSERT INTO dbo.CharReplace (CharToReplace, CharReplacement)

  SELECT .... -- put here your replacement rules

  SELECT REPLACE(datename(w,t.data), C.CharToReplace , C.CharReplacement )

  FROM Table t, CharReplace C

  GRUOP BY REPLACE(datename(w,t.data), C.CharToReplace , C.CharReplacement )

  _____________
  Code for TallyGenerator

• creliuc ciprian

  SSC Veteran

  Points: 248

  More actions

  February 1, 2006 at 12:21 am

  #618050

  Thanks Kenneth, your solution worked !