How to strip trailing 0's from a column from within an SQL Statement 1

[darrellblackhawk]

I'm modifying some SQL Reporting Services reports that present data from a Great Plains accounting system.

GP uses SQL Server as its datastore - ergo - Reporting Services.

Here's my dilemma:

Great Plains contains: three columns for phone numbers & one for fax numbers per customer and customer contact records.

Unfortunately, the way they implemented phone number fields is a bit cumbersome.

All phone nuimbers are represented as 14 character strings and are stored such as 65087332222200 with a mask of (XXX) XXX-XXXX ext (XXXX) for an expected display of (650) 873-3222 ext 22. (Only #'s are allowed in the UI's entry fields for phone numbers - i.e. can't use spaces to overwrite the 0's)

The way it actually displays is (650) 873-3222 ext 2200 - which is obviously not the desired outcome.

As you can tell, this will be a bit confusing for any user of the report.

Below I've pasted a 'knarley' select statement which I want to mask off any trailing 0's from phone fields.

Anybody have any ideas of how to do this in-line and not require function calls or 'Step-Stripping'?

I'm still growing in SQL’s Lingua Franca - so I apologize in advance if this is simple.


Thanks.

Darrell

select
 abs_sales_header.soptype, 
 abs_sales_header.sopnumbe, 
 abs_sales_header.docid, 
 abs_sales_header.docdate, 
 abs_sales_header.pymtrmid, 
 abs_sales_header.custnmbr, 
 abs_sales_header.custname, 
 abs_sales_header.cstponbr, 
 abs_sales_header.mstrnumb, 
 abs_sales_header.prbtadcd, 
 abs_sales_header.prstadcd,
 abs_sales_header.cntcprsn, 
 abs_sales_header.shiptoname, 
 abs_sales_header.address1, 
 abs_sales_header.address2, 
 abs_sales_header.address3, 
 abs_sales_header.city, 
 abs_sales_header.state, 
 abs_sales_header.zipcode, 
 [red]abs_sales_header.phnumbr1, -- { Strip these of trailing 0's
 abs_sales_header.phnumbr2,
 abs_sales_header.phone3,
 abs_sales_header.faxnumbr, [/red] 
 abs_sales_header.docamnt, 
 abs_sales_header.salsterr, 
 abs_sales_header.slprsnid, 
 abs_sales_header.noteindx, 
 abs_sales_header.TableSource, 
 abs_sales_line.lnitmseq,
 abs_sales_line.itemnmbr, 
 abs_sales_line.itemdesc, 
 abs_sales_line.TimeBlock, 
 TimeBlockAMPM = 
  CASE 
   when abs_sales_line.TimeBlock LIKE '%AM%' THEN 'AM'
   when abs_sales_line.TimeBlock LIKE '%PM%' THEN 'PM' 
   when abs_sales_line.TimeBlock LIKE '%N%' THEN 'PM' 
  ELSE ''
 END, 
 abs_sales_line.unitprce, 
 abs_sales_line.xtndprce, 
 abs_sales_line.quantity, 
 abs_sales_line.WeekStartDate, 
 abs_sales_line.DayofWeek, 
 sy03900.txtfield, 
 abs_sales_copy_entry.campaignid, 
 abs_sales_copy_entry.campaignname, 
 abs_sales_copy_entry.sendtape, 
 abs_sales_copy_entry.sendaffidavit, 
 abs_sales_copy_entry.phonetic, 
 abs_sales_copy_entry.salescomment2, 
 abs_sales_copy_entry.confirmed, 
 abs_sales_copy_entry.sendcopy, 
 rm00102auth.cntcprsn AS authorized, 
 sy01200auth.inet7 AS authorizedtitle, 
 rm00102attn.cntcprsn AS attention, 
 pm00200.vendorid, 
 pm00200.vendname, 
 pm00200.city AS vendorcity, 
 pm00200.comment1, 
 rm00103.custblnc
 FROM abs_sales_line INNER JOIN
  abs_sales_header ON abs_sales_line.soptype = abs_sales_header.soptype AND 
  abs_sales_line.sopnumbe = abs_sales_header.sopnumbe LEFT OUTER JOIN
  sy03900 ON abs_sales_header.noteindx = sy03900.noteindx LEFT OUTER JOIN
  rm00102 rm00102auth ON abs_sales_header.custnmbr = rm00102auth.custnmbr AND 
  abs_sales_header.prstadcd = rm00102auth.adrscode LEFT OUTER JOIN
  rm00102 rm00102attn ON abs_sales_header.custnmbr = rm00102attn.custnmbr AND 
  abs_sales_header.prbtadcd = rm00102attn.adrscode LEFT OUTER JOIN
  sy01200 sy01200auth ON abs_sales_header.custnmbr = sy01200auth.Master_ID AND
  abs_sales_header.prstadcd = sy01200auth.adrscode AND 
  sy01200auth.Master_Type = 'CUS' LEFT OUTER JOIN
  abs_sales_copy_entry ON abs_sales_header.sopnumbe = abs_sales_copy_entry.sopnumbe AND
  abs_sales_header.soptype = abs_sales_copy_entry.soptype LEFT OUTER JOIN
  pm00200 ON LEFT(abs_sales_line.itemnmbr, 7) = LEFT(pm00200.vendorid, 7) LEFT OUTER JOIN
  rm00103 ON abs_sales_header.custnmbr = rm00103.custnmbr
    WHERE (abs_sales_header.soptype = 3 OR abs_sales_header.soptype = 4) AND
         abs_sales_header.TableSource = @PostingStatus AND
         abs_sales_header.sopnumbe >= @StartInvoice AND
         abs_sales_header.sopnumbe <= @EndInvoice AND abs_sales_header.docdate >= @StartDate AND
         abs_sales_header.docdate <= (@EndDate + 1)
    ORDER BY abs_sales_header.sopnumbe, abs_sales_line.WeekStartDate, TimeBlockAMPM

 

[vongrunt]

How do you know that ext 2200 always represents 22? Perhaps it is 220 or even really 2200?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[darrellblackhawk]

It's a matter of how Great Plains implements phone number fields.

When the user enter a phone number of: 510-783-0207 ext 33;
as shown in the image below, Great Plains fills the field with trailing zeros. It's a bear!

 

[darrellblackhawk]

Actually the only way to know for sure is to have a paper trail or make a note in one of the additional comment fields. (Maybe there is a configuration item to set phone number fields to fill with spaces, but I've yet to find it.)

Darrell

 

[gmmastros]

I wonder what the datatype is for the phone number field.

If it's VarChar, then

Declare @Original VarChar(20)
Set @Original = '65087332222200'
Select Left(@Original, 12)

If it's BigInt, then...

Declare @Original BigInt
Set @Original = 65087332222200
Select Left(Convert(VarChar(20), @Original),12)

Basically, this method will convert to string and get the first 12 characters.

Alternative method for BigInt.

Declare @Original BigInt
Set @Original = 65087332222200
Select @Original /100

If the data is stored as decimal or numeric, then...

Declare @Original Numeric(20,0)
Set @Original = 65087332222200
Select Convert(BigInt, @Original / 100)


Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[darrellblackhawk]

It's a 14 byte/(unicode) string, but is forced to numerics and padded with zeros to the full length of the field.

A procedural policy could be put in place so users pad the extension with zeros when entering, but we all know what would happen with that.

After starting this thread and reviewing what vongrunt said (How do you know that ext 2200 always represents 22? Perhaps it is 220 or even really 2200? ), I'll have to rethink this.


Darrell

 

[SQLBill]

By the way, there is a forum or two for Great Plains here on Tek-Tips. We have found out that Great Plains TSQL is not the same as SQL Server TSQL. While Great Plains is based on Microsoft's SQL Server, there are differences and they are enough that there are forums just for Great Plains. I suggest you post your query there. It might save you problems.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

here is one way of stripping trailing 0's regardless of length of the field
replace zero with spaces right trim and replace spaces with zeros that way the trailing zeros will be gone
Run code below to see what I mean

declare @v varchar(10)
select @v= '1450055000'

select replace(rtrim(replace(@v,0,' ')),' ',0)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

And this one...

declare @v varchar(10)
select @v= '1450055000'

Select Reverse(Convert(int, Reverse(@v)))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Hehe, I was waiting for someone using REVERSE()...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[darrellblackhawk]

Thanks for all the helpful posts, but I have to dig in a little deeper. (Can't just strip the trailing zeros - i.e. an extension is 100 - hmmm...)

Thanks again