how to trim only the leading zeros in the tsql query

Question

Post reply

how to trim only the leading zeros in the tsql query

parthmeister

SSC Veteran

Points: 290
More actions
March 27, 2011 at 5:49 pm

#233777

Hi Folks,
i have a tsql query as below, see the highlighted bold:
SELECT RTRIM(ISNULL(dbo.Person.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.Person.Pers_LastName, '')) AS Pers_FullName,
RTRIM(REPLACE(REPLACE(ISNULL(dbo.Phone.Phon_CountryCode, '') + LTRIM(REPLACE(ISNULL(phon_areacode, ''),'0',''))
+ [highlight=#ffff11]ISNULL(dbo.Phone.Phon_Number, ''), '-', ''), ' ', ''))[/highlight] AS Phon_FullNumber, dbo.Person.Pers_PersonId, dbo.Person.Pers_PrimaryUserId, dbo.Person.pers_SecTerr, dbo.Person.Pers_CreatedBy,
dbo.Person.Pers_ChannelID, dbo.Person.Pers_Deleted, dbo.Company.Comp_CompanyId, dbo.Company.Comp_Name, dbo.Phone.Phon_CompanyID,
dbo.Phone.Phon_PersonID, dbo.Phone.Phon_AccountId, dbo.Company.Comp_PrimaryUserId, dbo.Company.Comp_SecTerr, dbo.Company.Comp_CreatedBy,
dbo.Company.Comp_ChannelID, dbo.Account.Acc_Name, dbo.Account.Acc_PrimaryUserId, dbo.Account.Acc_Secterr, dbo.Account.Acc_CreatedBy,
dbo.Account.Acc_ChannelId
FROM dbo.Phone LEFT OUTER JOIN
dbo.Company ON dbo.Phone.Phon_CompanyID = dbo.Company.Comp_CompanyId LEFT OUTER JOIN
dbo.Person ON dbo.Phone.Phon_PersonID = dbo.Person.Pers_PersonId LEFT OUTER JOIN
dbo.Account ON dbo.Phone.Phon_AccountId = dbo.Account.Acc_AccountID
WHERE (dbo.Person.Pers_Deleted IS NULL) AND (dbo.Phone.Phon_Deleted IS NULL) AND phon_companyid = 1252
what i want to achive with this query is i want to trim the leading zero only from the number below
0425236789
so that when i run the first select query if get the number 61425263215 and not below.
610425263215
also there are landline numbers with spaces and '-' in those numbers, so we have the highlighted statement as ISNULL(dbo.Phone.Phon_Number, ''), '-', ''), ' ', ''))
i know we can use patindex like substring(phon_number, patindex('%[^0]%',phon_number), 10)
but i am failing to achieve it by merging the two i.e. ISNULL(dbo.Phone.Phon_Number, ''), '-', ''), ' ', ''))
with substring(phon_number, patindex('%[^0]%',phon_number), 10)
can any one please help me out Restructuring the query
regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

yewang80 SSC Rookie Points: 43 More actions · Answer 1

maybe using something like

case when dbo.Phone.Phon_Number like '0%' then substring (2,##) else dbo.Phone.Phon_Number end

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

An alternative would be to cast as a BIGINT.

e.g.

DECLARE @phonenumber VARCHAR(12), @areacode VARCHAR(5), @countrycode VARCHAR(2)

SET @phonenumber = '367894'

SET @areacode = '04252'

SET @countrycode = '61'

SELECT

CAST(CAST(REPLACE(REPLACE(LTRIM(RTRIM(@countrycode)),'-',''),' ','') AS BIGINT) AS VARCHAR(2)) +

CAST(CAST(REPLACE(REPLACE(LTRIM(RTRIM(@areacode)),'-',''),' ','') AS BIGINT) AS VARCHAR(5)) +

CAST(CAST(REPLACE(REPLACE(LTRIM(RTRIM(@phonenumber)),'-',''),' ','') AS BIGINT) AS VARCHAR(12))

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

srikant maurya Hall of Fame Points: 3293 More actions · Answer 3

try below query

DECLARE @areacode VARCHAR(32)

SET @areacode= '00000012345'

SELECT Right(@areacode, Len(@areacode) + 1 - Patindex('%[^0]%', @areacode))

Lowell SSC Guru Points: 323518 More actions · Answer 4

I like srikant maury's a method of searching for the patindex; nice and clean.

If you are sure no dashes or parenthesis are stored in the string,

yet another way is convert to int and then back to a varchar is easier, and handles multiple preceeeing zeros:

--Results :12345

DECLARE @areacode VARCHAR(32)

SET @areacode= '00000012345'

SELECT convert(varchar(32),convert(int,@areacode))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!