Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: How do I pull only the first 7 Numbers/Characters

    Hi,

    I am working in SQL and there is a table that contains both numbers and dashes. I have a 9 digit number that looks like the following 1234-054-0 I want to pull the first 7 digits so the output looks like 1234-054

    I want to exclude the -0 or -1 from all of the data in this particular table. How owuld I go about doing this? I tried using the substr function but I don't receive any results. I have something like substr(tu.trial_unit_reference, 4,4) but it doesn't work for the above and I am assuming this is because of the dashes?

    Can anyone help me?

    Thanks

    D

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first seven would be
    Code:
    SUBSTRING(tu.trial_unit_reference,1,7)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He wants the first seven digits, not the first seven characters.
    Code:
    left(tu.trial_unit_reference, 8)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    To remove the -0 and -1, this may give you an idea. It may remove -0s and -1s that you do not want to remove, so think about its use. There are ways to deal with those issues, but you did not clearly state which -0 and -1 you wanted to remove.
    Code:
    declare @ImportantNumber varchar(25)='1234-054-0'
    
    select substring(replace(replace(@ImportantNumber,'-0',''),-1,''),1,7)
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use select substring(yourcolumn,1,7) from your table name

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    Use select substring(yourcolumn,1,7) from your table name
    when you read through the replies already made in this thread, you must have unconsciously skipped over post #2, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    28
    OK.. I am confused here as I do not see anything that states numbers vs. characters and furthermore if that field has dashes "-" then it is safe to say that it is a CHAR or VARCHAR of some sort. Based upon that the Substring(TableName.FieldName, 1, 7) should do the trick. The only problem that could arise from this if the data is not 100% in the 9999-99-99 format. If that is the case then you will need to throw in a case statement which looks at the length of the data and then process it accordingly. But then again... I am not a programmer or DBA so what do I really know.


    AmcAmx

  8. #8
    Join Date
    Sep 2011
    Posts
    71

    Question Try that script

    Hello you can try that sql script


    SELECT CAST(LEFT(CAST(YourInt AS VARCHAR(100)), 7) AS INT)


  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by paultech View Post
    Hello you can try that sql script


    SELECT CAST(LEFT(CAST(YourInt AS VARCHAR(100)), 7) AS INT)
    when you read through the replies already made in this thread, you must have unconsciously skipped over post #3, eh

    Seems to be a small epidemic of this going around....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't suppose that anyone has noticed that the original poster has not been back to this thread...

    in fact, the original poster has not even been back to this site for almost two weeks...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Perhaps you should try
    Code:
    select SUBSTRING(tu.trial_unit_reference,1,7)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •