0

I have string like this:

00876H873 - I want 876H873

00876 876500 - I want 876 876500

0000HUJ8 9IU877 8UJH - I want HUJ8 9IU877 8UJH

I use:

SELECT REPLACE(LTRIM(REPLACE(ColumnName, '0', ' ')),' ', '0')

This works in the first and second cases, i.e. if there is no space or exactly one space; however, if there are multiple spaces (such as the third example), I am not getting the right answer.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
user3812887
  • 439
  • 12
  • 33
  • 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) – AHiggins Aug 25 '16 at 12:02
  • yeah; I took it from there only; this does not work when I have multiple spaces; any suggestion on how to handle the 3 rd case, with 2 spaces – user3812887 Aug 25 '16 at 12:26
  • My input is this: '00 11011 0' When I use this, I get an space before the start of the result: '_11011_0' The underscore is a space in the result. I want to remove this space at the beginning; I am okay with the later space (between the last 0 in the result and the 1 that immediately precedes it) – user3812887 Aug 25 '16 at 15:12

1 Answers1

0

The duplicate flag raised in the comments will point you to a working answer, but you need to expand the length of SUBSTRING() to handle your data type.

For example, if you tried to copy the code exactly and run:

DECLARE @x VARCHAR(100)
SET @x = '0000HUJ8 9IU877 8UJH'
SELECT SUBSTRING(@x, PATINDEX('%[^0]%', @x), 10) -- note this uses '10'

You'll get the wrong result, HUJ8 9IU87. This has nothing to do with the extra space: you simply are telling SUBSTRING() that you want ten characters, and it's giving you ten characters (which coincidentally goes up to the space).

If you expand the SUBSTRING() to a longer value, as in the following code, it will work as expected:

DECLARE @x VARCHAR(100)
SET @x = '0000HUJ8 9IU877 8UJH'
SELECT SUBSTRING(@x, PATINDEX('%[^0]%', @x), 100) -- note this now uses '100'

Set the length argument for SUBSTRING() to be whatever you need from your data type, and it will work correctly: it returns HUJ8 9IU877 8UJH.

EDIT:

if you want to remove leading spaces, in addition to leading zeroes, you'll need to change the formula to LTRIM(SUBSTRING(LTRIM(@x), PATINDEX('%[^0]%', @x), 100)): I put LTRIM() in there twice to handle leading spaces before removing zeroes, and after removing zeroes.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • but this adds space at the start; for example: SELECT '00 11011 0', SUBSTRING('00 11011 0', PATINDEX('%[^0]%', '00 11011 0'), 100) – user3812887 Aug 25 '16 at 15:05
  • I get a space at the beginning of the result in this case '00 11011 0' – user3812887 Aug 25 '16 at 15:06
  • It doesn't add a space ... It does what we told it to, which is remove leading zeroes. If the leading zeroes happen to go up to a space, then that's what it gives you. If you need to remove leading spaces after the fact, wrap it in an LTRIM(). – AHiggins Aug 25 '16 at 15:15
  • Thanks, that's what I did: SELECT LTRIM(SUBSTRING('00 11011 0', PATINDEX('%[^0]%', '00 11011 0'), LEN('00 11011 0'))) – user3812887 Aug 25 '16 at 15:34
  • Thanks, I have now solved this issue. Appreciate it; I added LTRIM – user3812887 Aug 25 '16 at 15:34
  • Excellent! If this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. – AHiggins Aug 25 '16 at 16:54