Post reply

how to trim only the leading zeros in the tsql query

  • 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

  • March 27, 2011 at 5:57 pm

    #1304141

    maybe using something like

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

  • March 28, 2011 at 4:14 am

    #1304254

    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/

  • March 28, 2011 at 4:29 am

    #1304257

    try below query

    DECLARE @areacode VARCHAR(32)

    SET @areacode= '00000012345'

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

  • March 28, 2011 at 6:17 am

    #1304305

    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!

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

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