Wednesday, May 28, 2008

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.

1 comment:

blitzkreig said...
This comment has been removed by a blog administrator.

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape