Posted By

aunlead on 10/08/10


Tagged

sql


Versions (?)

To sort varchar fields which contain numeric data


 / Published in: SQL
 

To sort varchar fields which contain numeric data & returning XML

  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  

You need to login to post a comment.