3

I have a accountNo column varchar(50)

sample data 

000qw33356
034453534u
a56465470h
00000000a1

I need output like..

qw33356
34453534u
a56465470h
a1

I have limitation that i can not use while loop in side UDF as this is creating performance issue .

James Z
  • 12,209
  • 10
  • 24
  • 44
sandeep rawat
  • 4,797
  • 1
  • 18
  • 36

5 Answers5

4

If your data doesn't contains spaces you can use:

select replace(ltrim(replace(data, '0', ' ')),' ', '0')

If there are spaces, you could replace them first to something else that doesn't exist and then replace back at the end.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • What happens when one of the value has already one space for example 'a564654 70h' – StackUser May 19 '16 at 13:47
  • @StackNewUser Like I said, in that case the space should be first replaced to something else and then back to space in the end. Otherwise it gets translated into 0. – James Z May 19 '16 at 13:52
1

Here's a somewhat cute variant that doesn't need a replacement character (although that's the one I'd usually use):

declare @t table (Val varchar(20))
insert into @t(Val) values
('000qw33356'),
('034453534u'),
('a56465470h'),
('00000000a1')

select SUBSTRING(Val,PATINDEX('%[^0]%',Val),9000)
from @t

Results:

--------------------
qw33356
34453534u
a56465470h
a1

Basically, we just take a substring from the first non-0 character to the end (assuming 9000 is larger than your input string length)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

You can use PATINDEX to find fist not zero character. And then get a SUBSTRING from this point to the end of a string:

SUBSTRING(accountNo, PATINDEX('%[^0]%',accountNo),1000)
valex
  • 23,966
  • 7
  • 43
  • 60
0

Try it like this

DECLARE @tbl TABLE(Test VARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('000qw33356')
,('034453534u')
,('a56465470h')
,('00000000a1');

SELECT SUBSTRING(Test,PATINDEX('%[^0]%',Test),1000)
FROM @tbl
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

You can use PATINDEX

DECLARE @table TABLE(Data VARCHAR(MAX));
INSERT INTO @table VALUES
('000qw33356'),
('034453534u'),
('a56465470h'),
('00000000a1')

SELECT SUBSTRING(Data,PATINDEX('%[^0]%',Data),8000)
FROM @table
Praveen ND
  • 540
  • 2
  • 10
  • valex, Damien_the_unbeliever and me, we have posted exactly the same suggestion in the same minute (almost). Why are you repeating this answer 30 minutes later? – Shnugo May 19 '16 at 14:53