Query Help

• yogi123

  SSCrazy

  Points: 2932

  More actions

  September 3, 2013 at 12:39 pm

  #298383

  Hello Everyone

  I need to develop something in my logic. I have one code '7MTWRF', this is string and i need to convert as

  M:7;T:7;W:7;R:7;F:7

  please help me to do so

  Thanks

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  September 3, 2013 at 12:51 pm

  #1647125

  This can't be as simple as it appears, so I have to question what is missing. If it really is this simple, just use the SUBSTRING function to concatenate the string you want to return back.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 3, 2013 at 1:07 pm

  #1647128

  As Ed said, this might be more complicated than this. However, this might give you an idea on what you need.

  WITH CTE( String) AS(

  SELECT '7MTWRF'

  ),

  Tally AS(

  SELECT TOP 100 ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) n

  FROM sys.objects

  ),

  OneString( String) AS(

  SELECT ';' + SUBSTRING(String,n,1) + ':' + LEFT( String, 1)

  FROM CTE c

  JOIN Tally t ON LEN(c.String) >= n

  WHERE n > 1

  FOR XML PATH('')

  )

  SELECT STUFF(String, 1, 1, '')

  FROM OneString

  Check the following reference: Creating a comma-separated list[/url]

  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