To sort varchar fields which contain numeric data


/ Published in: SQL
Save to your folder(s)

To sort varchar fields which contain numeric data & returning XML


Copy this code and paste it in your HTML
  1. eg.
  2. QuestionId QuestionCode Question IsActive
  3. 1 5 ABC 1
  4. 2 AC5 ABC2 1
  5. 3 60 ABCD 1
  6. 4 A4 ABCF 1
  7. 5 1 ABCASD 1
  8.  
  9.  
  10. SELECT
  11. (
  12. SELECT
  13. QuestionId AS QuestionID, -- INT
  14. QuestionCode AS QuestionCode, -- VARCHAR
  15. Question AS Question, -- VARCHAR
  16. IsActive AS IsActiveValue -- BIT
  17. FROM
  18. Questions
  19. WHERE
  20. IsActive = 1 ORDER BY -- [To sort varchar fields]
  21. CASE ISNUMERIC(QuestionCode)
  22. WHEN 1 THEN Replicate(CHAR(35), 100 - LEN(QuestionCode)) + QuestionCode
  23. ELSE QuestionCode
  24. END
  25. FOR XML PATH('Question'), TYPE
  26. )
  27. FOR XML PATH('BOQuestionList'), TYPE

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.