Remove zeros from a string in SQL server

Question

Remove zeros from a string in SQL server

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm normalizing a field that is for phones, a custom function is used, the phones should be with 8 caraceteres, but it is left with 12 and the last 4 are 0 (zeros) Example:
input format = ["11232323230000"] output format (applied function) = 232323230000 how it should look = 23232323

to try to eliminate the last 4 add the following line in the function

 IF (right(@F_NormPhone,8) = '0000') and left(@f_normphone,1) = '4'
        select  @F_NormPhone = SUBSTRING(@F_NormPhone,8, LEN(@F_NormPhone) - 4)

but even so, it is not eliminating the "0"

I leave part of the function

for example a more real phone number, send us with the format "// 011455656100 '' '' ''"

IF CHARINDEX('/',@F_NormPhone, 1) >0 
    SELECT @F_NormPhone = SUBSTRING(@F_NormPhone,1, CHARINDEX('/', @F_NormPhone, 1) - 1)

IF CHARINDEX('int',@F_NormPhone, 1) >0 
    SELECT @F_NormPhone = SUBSTRING(@F_NormPhone,1, CHARINDEX('int', @F_NormPhone, 1) - 1)


IF (Left(@F_NormPhone,4) = '0000')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)

IF (Left(@F_NormPhone,3) = '000')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,3, LEN(@F_NormPhone) - 2)

IF (Left(@F_NormPhone,2) = '00')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,2, LEN(@F_NormPhone) - 1)

IF (Left(@F_NormPhone,7) = '1115411')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,8, LEN(@F_NormPhone) - 7)

IF (Left(@F_NormPhone,6) = '545411')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,7, LEN(@F_NormPhone) - 6)

IF (Left(@F_NormPhone,6) = '115411')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,7, LEN(@F_NormPhone) - 6)

IF (Left(@F_NormPhone,5) = '54011')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,6, LEN(@F_NormPhone) - 5)

IF (Left(@F_NormPhone,5) = '54911')
    select  @F_NormPhone = '15' + SUBSTRING(@F_NormPhone,6, LEN(@F_NormPhone) - 5)

--IF (Left(@F_NormPhone,3) = '549')
--  select  @F_NormPhone = '0' + SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)


IF (Left(@F_NormPhone,4) = '0054')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,5, LEN(@F_NormPhone) - 4)

IF (Left(@F_NormPhone,5) = '05411')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,6, LEN(@F_NormPhone) - 5)

IF (Left(@F_NormPhone,4) = '5411')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,5, LEN(@F_NormPhone) - 4)

IF (Left(@F_NormPhone,3) = '054')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)
IF (Left(@F_NormPhone,3) = '911')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)
        IF (Left(@F_NormPhone,3) = '954')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)

IF (Left(@F_NormPhone,2) = '54' AND Len(@F_NormPhone) > 8)
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,3, LEN(@F_NormPhone) - 2)

IF (Left(@F_NormPhone,4) = '0011')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,5, LEN(@F_NormPhone) - 4)

IF (Left(@F_NormPhone,3) = '011')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)

IF (Left(@F_NormPhone,2) = '11')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,3, LEN(@F_NormPhone) - 2)

IF (Left(@F_NormPhone,2) = '01') and len(@f_NormPhone) > 2
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,3, LEN(@F_NormPhone) - 2)

IF (Left(@F_NormPhone,3) = '015')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,4, LEN(@F_NormPhone) - 3)

IF (Left(@F_NormPhone,4) = '0015')
    select  @F_NormPhone = SUBSTRING(@F_NormPhone,5, LEN(@F_NormPhone) - 4)

sql

asked by Emmanuel 10.07.2018 в 01:37

source

2 answers

How to run the proxy included in the package.json for axios calls How to declare a ChoiceField on models.py

score 0 · Answer 1

I'll give you an example that can help you, but only considering that at the beginning you find two and at the end 4 zeros.

 declare @vceross varchar(max)='11232323230000'
 declare @vlongitud int

 select @vlongitud=len(@vceross)

 IF (right(@vceross,4) = '0000')
 BEGIN
 SELECT @vceross=  SUBSTRING(@vceross,0,@vlongitud-3)
 END

 IF left(@vceross,2)='11'
 BEGIN
 SELECT @vceross=SUBSTRING(@vceross,3,len(@vceross))
 END

score 0 · Answer 2

Dear all solved, the solution was to make an update outside the source, because if those 4 "0" (0000) pertain to the phone number, the telephone normalizing function would stop working. I would normalize but with incorrect numbers.

Update table1 set Telphone = left (Telphone, len (Telphone) - 4) where right (Telphone, 4) = '0000'