ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Sort (Order By) VARCHAR column contains Number in SQL Server<p>Hi ayakamacy,&nbsp;</p> <p>&nbsp;</p> <p>You can try the below query.</p> <pre class="brush: sql">SELECT * FROM TblR8Pure ORDER BY Jumper ASC;</pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/Wed, 07 Sep 2016 03:51:01 GMTSort (Order By) VARCHAR column contains Number in SQL Server<p>Hi <a class="username" rel="ayakamacy">ayakamacy</a>,</p> <p>Refer the below Query.</p> <p>You can perform some tricks by converting to a numeric after you discover the location of the first non-numeric. Appending a random character at the end makes it treat all strings the same even if the original string did not contain an alphabetic.</p> <p><span style="text-decoration: underline;">SQL</span></p> <pre class="brush: sql">DECLARE @TblR8Pure AS TABLE(WireID INT,PadNo VARCHAR(20),Pattern INT,Tester VARCHAR(20)) INSERT INTO @TblR8Pure VALUES(1,&#39;3_29&#39;,158,&#39;40&#39;) INSERT INTO @TblR8Pure VALUES(2,&#39;3_18&#39;,173,&#39;41&#39;) INSERT INTO @TblR8Pure VALUES(3,&#39;3_33&#39;,155,&#39;44&#39;) INSERT INTO @TblR8Pure VALUES(4,&#39;3_7&#39;,182,&#39;45&#39;) INSERT INTO @TblR8Pure VALUES(5,&#39;3_19&#39;,169,&#39;47&#39;) INSERT INTO @TblR8Pure VALUES(6,&#39;3_10&#39;,181,&#39;48&#39;) INSERT INTO @TblR8Pure VALUES(7,&#39;3_2&#39;,189,&#39;50&#39;) INSERT INTO @TblR8Pure VALUES(8,&#39;3_21&#39;,172,&#39;51&#39;) INSERT INTO @TblR8Pure VALUES(9,&#39;3_16&#39;,175,&#39;52&#39;) INSERT INTO @TblR8Pure VALUES(10,&#39;3_22&#39;,164,&#39;54&#39;) INSERT INTO @TblR8Pure VALUES(16,&#39;3_19&#39;,169,&#39;225;219&#39;) INSERT INTO @TblR8Pure VALUES(19,&#39;3_10&#39;,181,&#39;227&#39;) INSERT INTO @TblR8Pure VALUES(17,&#39;3_2&#39;,189,&#39;229;355;320&#39;) INSERT INTO @TblR8Pure VALUES(18,&#39;3_21&#39;,172,&#39;234;362;318&#39;) INSERT INTO @TblR8Pure VALUES(11,&#39;3_28&#39;,157,&#39;100&#39;) INSERT INTO @TblR8Pure VALUES(12,&#39;3_20&#39;,170,&#39;46,128&#39;) INSERT INTO @TblR8Pure VALUES(13,&#39;3_14&#39;,177,&#39;42,128,28&#39;) INSERT INTO @TblR8Pure VALUES(14,&#39;3_37&#39;,149,&#39;49,56,129&#39;) INSERT INTO @TblR8Pure VALUES(15,&#39;3_34&#39;,152,&#39;53,122,237&#39;) SELECT * FROM @TblR8Pure ORDER BY CONVERT(INT, LEFT(Tester, PATINDEX(&#39;%[^0-9]%&#39;, Tester + &#39;z&#39;)-1))</pre> <p><span style="text-decoration: underline;">Output</span></p> <table width="229"> <tbody> <tr> <td width="50">WireID</td> <td width="48">PadNo</td> <td width="53">Pattern</td> <td width="78">Tester</td> </tr> <tr> <td>1</td> <td>3_29</td> <td>158</td> <td>40</td> </tr> <tr> <td>2</td> <td>3_18</td> <td>173</td> <td>41</td> </tr> <tr> <td>13</td> <td>3_14</td> <td>177</td> <td>42,128,28</td> </tr> <tr> <td>3</td> <td>3_33</td> <td>155</td> <td>44</td> </tr> <tr> <td>4</td> <td>3_7</td> <td>182</td> <td>45</td> </tr> <tr> <td>12</td> <td>3_20</td> <td>170</td> <td>46,128</td> </tr> <tr> <td>5</td> <td>3_19</td> <td>169</td> <td>47</td> </tr> <tr> <td>6</td> <td>3_10</td> <td>181</td> <td>48</td> </tr> <tr> <td>14</td> <td>3_37</td> <td>149</td> <td>49,56,129</td> </tr> <tr> <td>7</td> <td>3_2</td> <td>189</td> <td>50</td> </tr> <tr> <td>8</td> <td>3_21</td> <td>172</td> <td>51</td> </tr> <tr> <td>9</td> <td>3_16</td> <td>175</td> <td>52</td> </tr> <tr> <td>15</td> <td>3_34</td> <td>152</td> <td>53,122,237</td> </tr> <tr> <td>10</td> <td>3_22</td> <td>164</td> <td>54</td> </tr> <tr> <td>11</td> <td>3_28</td> <td>157</td> <td>100</td> </tr> <tr> <td>16</td> <td>3_19</td> <td>169</td> <td>225;219</td> </tr> <tr> <td>19</td> <td>3_10</td> <td>181</td> <td>227</td> </tr> <tr> <td>17</td> <td>3_2</td> <td>189</td> <td>229;355;320</td> </tr> <tr> <td>18</td> <td>3_21</td> <td>172</td> <td>234;362;318</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/Wed, 07 Sep 2016 03:53:01 GMTSort (Order By) VARCHAR column contains Number in SQL Server<p>Hi&nbsp;<a class="username" rel="dharmendr">dharmendr, sorry but its still the same.</a></p> <p>&nbsp;</p> <p><img src="https://s10.postimg.org/84dtdypop/img3.png" alt="result" width="537" height="564" /></p>https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/Wed, 07 Sep 2016 04:13:36 GMTSort (Order By) VARCHAR column contains Number in SQL Server<p>I have modified my previous reply. Refer the below link.</p> <p><a href="http://www.aspforums.net/Threads/151246/Sql-Order-By/Replies/2#Replies">http://www.aspforums.net/Threads/151246/Sql-Order-By/Replies/2#Replies</a></p> <p>&nbsp;</p> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/Thu, 08 Sep 2016 07:14:55 GMTSort (Order By) VARCHAR column contains Number in SQL Server<p>Hi&nbsp;<a class="username" rel="dharmendr">dharmendr,</a></p> <p>I also got the answer before I read your reply.</p> <p><strong>This is my Answer:</strong></p> <p><img src="http://i.stack.imgur.com/QD4vi.png" alt="MyAnswer" width="537" height="639" /></p> <p><strong>Your Answer:</strong></p> <p><img src="https://s22.postimg.org/hgbnto8xt/His_Answer.png" alt="YourAnswer" width="744" height="644" /></p> <p>Thank you so much for the effort ! =)</p>https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/https://www.aspforums.net:443/Threads/151246/Sort-Order-By-VARCHAR-column-contains-Number-in-SQL-Server/Thu, 08 Sep 2016 23:49:01 GMT