String concatenation + ASCII values

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  December 19, 2008 at 12:04 am

  #71115

  Hi All,

  I have noted an intersting thing.

  create table #emp

  (

  name varchar(25)

  )

  insert into #emp

  select 'karthik'

  union

  select 'anu'

  union

  select 'kavi'

  union

  select 'raj'

  union

  select 'guru'

  declare @s-2 varchar(100)

  select @s-2 = ''

  select @s-2 = @s-2 + name

  from #emp

  select @s-2

  Output:

  anugurukarthikkaviraj

  #2:-

  create table #emp

  (

  name varchar(25)

  )

  insert into #emp

  select '1'

  union

  select 'Har'

  union

  select '2'

  union

  select '7'

  union

  select 'abc'

  union

  select '#'

  union

  select '@'

  declare @s-2 varchar(100)

  select @s-2 = ''

  select @s-2 = @s-2 + name

  from #emp

  select @s-2

  output:

  #@127abcHar

  if you look at the two outputs , the data has been ordered automatically.

  i thought sqlserver used ASCII value by default to sort the data's.

  But the ascii values are

  select ascii('#') - 35

  select ascii('@') - 64

  select ascii('1') - 49

  select ascii('2') - 50

  select ascii('7') - 55

  select ascii('har') - 104

  select ascii('abc') - 97

  if sqlserver use ASCII value to sort the data ,then the output would be

  35 49 50 55 64 97 104

  #127@abchar

  but what we got

  #@127abcHar

  i.e symbols came as first.

  why ?

  Inputs are welcome !

  karthik

• ps.

  One Orange Chip

  Points: 29423

  More actions

  December 19, 2008 at 12:15 am

  #915005

  This is happening since u're using UNION which sorts the data and outputs only distinct values.

  Replcae UNION with UNION ALL and no sorting happens

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

  create table #emp

  (

  name varchar(25)

  )

  --drop table #emp

  insert into #emp

  select '1'

  union all

  select 'Har'

  union all

  select '2'

  union all

  select '7'

  union all

  select 'abc'

  union all

  select '#'

  union all

  select '@'

  declare @s-2 varchar(100)

  select @s-2 = ''

  select @s-2 = @s-2 + name

  from #emp

  select @s-2

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

  Output--

  1Har27abc#@

  ---

  
  Pradeep Singh

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  December 19, 2008 at 2:40 am

  #915059

  oops...i said blindly SQL sorts the data automatically... I have phrased my words wrongly...

  YES..UNION will sort the the data by using internal workt table.

  but the sorting order looks different...Thats what i wanted to know.

  karthik

• ps.

  One Orange Chip

  Points: 29423

  More actions

  December 19, 2008 at 3:00 am

  #915068

  I tried searching various places but nowhere i found that UNION sorts the data, it only performs a SELECT DISTICNT on the cummulative resultset.

  I was wrong there!!

  ---

  
  Pradeep Singh