0

I have have to compare two char(4), which are formatted differently. Thus I would like to one format so that it can be compared to the other. Basically I need to remove prefixed zeros and trailing space if present.

For instance:

The chars:

'001 ' and '1' should both compare to '1' after formatting

'001A' and '1A' should both compare to '1A' after formatting

'010 ' and '10' should both compare to '10' after formatting

'010A' and '10A' should both compare to '10A' after formatting

'100 ' and '100' should both compare to '100' after formatting

'100A' should compare to '100A' after formatting

select Replace(Ltrim(Replace(Rtrim(@charToBeFormatted), '0', ' ')), ' ', '0')

seems to work, but feels wasteful to replace strings twice and it really does not make the intention clear at all.

Can the result be achieved in a more efficient and/or elegant manner?

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
steenhulthin
  • 4,553
  • 5
  • 33
  • 52
  • @Benoit [LTRIM](http://msdn.microsoft.com/en-us/library/ms177827.aspx) and [RTRIM](http://msdn.microsoft.com/en-us/library/ms178660.aspx) do not take a second parameter. – Joe Stefanelli Jan 12 '12 at 18:08
  • @JoeStefanelli eh, not sure what you mean. Both LTRIM and RTRIM in the example takes only one parameter. Can you elaborate? (maybe a underlining the of the mentioned problem with lack of readability/clear intention? ?_? ) – steenhulthin Jan 12 '12 at 18:31
  • I was responding to a commment posted by @Benoit, which he has since deleted. – Joe Stefanelli Jan 12 '12 at 18:37
  • 1
    possible duplicate of [Removing leading zeroes from a field in a SQL statement](http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement) – Andriy M Jan 13 '12 at 05:58
  • Here's another one: [Algorithms to trim leading zeroes from a SQL field?](http://stackoverflow.com/questions/2457603/algorithms-to-trim-leading-zeroes-from-a-sql-field) – Andriy M Jan 13 '12 at 06:00
  • @AndriyM Thanks, I couldn't find the right keywords to search for I guess. I've voted to close as dupe of http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement – steenhulthin Jan 13 '12 at 07:31

1 Answers1

1

is the result supposed to be a number? than you can do something like this: select convert(int,'0010 ')

Mordechai
  • 718
  • 1
  • 8
  • 23
  • no, it is not supposed to be a number. The char '1A' does not make sense in my problem as an int. – steenhulthin Jan 12 '12 at 19:49
  • that's right, I missed that. Did you see this: http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement? reworking it you can make your query a little nicer: declare @var varchar(20)='0010 ' select Rtrim(RIGHT(@var,patindex('%[^0]%',@var))) – Mordechai Jan 13 '12 at 07:50