Advanced Search

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

Expand | Embed | Plain Text

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.