help in the below scenario

• pandeesh

  Say Hey Kid

  Points: 673

  More actions

  January 2, 2012 at 11:14 am

  #391231

  Hi,

  I have a table with a column called "DESC".

  My requirement is ,if the input is "abc",then the output should be [a][c].

  The datatype of the column is varchar(10).

  Please help me in this.

  Thanks

  Thanks,
  Pandeeswaran

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  January 2, 2012 at 11:57 am

  #1427483

  DECLARE @SampleData TABLE ([DESC] VARCHAR(10))

  INSERT INTO @SampleData

  VALUES ('abc'), ('u&w<yz')

  SELECT

  CA.Result.value('.', 'VARCHAR(30)')

  FROM

  @SampleData SD

  CROSS APPLY

  (

  SELECT

  -- Surround every character with [] using an index from the Tally derived table

  '[' + SUBSTRING(SD.[DESC], Tally.N, 1) + ']'

  FROM

  (

  -- Tally table to use for indexing the input string

  VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

  ) Tally(N)

  WHERE

  Tally.N <= DATALENGTH(SD.[DESC])

  -- Use FOR XML PATH trick to concatenate all surrounded characters together

  FOR XML PATH(''), TYPE

  ) CA(Result)

  Edit: removed useless empty string column from the code

• pandeesh

  Say Hey Kid

  Points: 673

  More actions

  January 2, 2012 at 12:05 pm

  #1427485

  Awesome! thanks!

  Thanks,
  Pandeeswaran