Trim Characters in SQL Server

How to Trim Characters in SQL Server

If you are looking for following solution than probably this post can help you out:

• How to Trim Characters in SQL Server
• How to Extract Part of String in SQL Server
• Returning only Numeric Part of String in SQL Server
• Trim String in SQL Server
• Trim Left and Right Portion of String in SQL Server
• How to Sort Numeric String in SQL Server.
  

Note: The Example I am going to discuss is the solution for above mention criteria, understand it and you can be able to derive solution for your problem.

I want to sort a string like this.

Nodes

1-49

100-249

1000-2499

10000+

250-499

2500-4999

50-99

500-749

5000-9999

750-999

Lets go step by step and then letter sum it up.

Step1: To undergo this task I need to extract characters from left till this “-“ or “+” character.

Let find length till this “-“

select distinct MyExpr,charindex('-',MyExpr)-1 as Nodes from MyTable

1-49	1
10000+	-1
750-999	3
5000-9999	4
250-499	3
1000-2499	4
500-749	3
2500-4999	4
100-249	3
50-99	2

So I also require separate statement for

select distinct MyExpr,charindex('-',MyExpr)-1 as Nodes from MyTable

1-49	-1
10000+	5
750-999	-1
5000-9999	-1
250-499	-1
1000-2499	-1
500-749	-1
2500-4999	-1
100-249	-1
50-99	-1

Now lets extract part of string by using substring function and case select statement.

select distinct MyExpr as Nodes,

substring(MyExpr,1,(

case

when charindex('-',MyExpr)-1 = '-1'

then charindex('+',MyExpr)-1

else charindex('-',MyExpr)-1 end

)

)

as 'NodesSort'

from MyTable

Order by 'NodesSort'

So this gives us extraction of Part of string from string. You can also see trim right portion of string.

1-49	1
100-249	100
1000-2499	1000
10000+	10000
250-499	250
2500-4999	2500
50-99	50
500-749	500
5000-9999	5000
750-999	750

Now casting the string and displaying in sorted order.

Casting string to int and applying order by for sorting.

select distinct MyExpr as Nodes,

-- SORT Logic

cast(

substring(MyExpr,1,(

case

when charindex('-',MyExpr)-1 = '-1'

then charindex('+',MyExpr)-1

else charindex('-',MyExpr)-1 end

)

)

as int) as 'NodesSort'

from MyTable

order by 'NodesSort'

1-49	1
50-99	50
100-249	100
250-499	250
500-749	500
750-999	750
1000-2499	1000
2500-4999	2500
5000-9999	5000
10000+	10000

This method is not optimal, as we are using functions within function to achieve task, but yet you can achieve the solution, if anyone of you know more better way to achieve same task than please share.