Post reply

Data Trimming Issue

  • January 6, 2011 at 12:29 pm

    #230140

    Hello,

    In my sql 2005 table's column, values for one column is in this form

    350315357.

    I need to display the values as 35031-5357.

    I need a - after 5 digits. Wht is the best function and how can i do it?

    Please let me know

    Thanks

  • January 6, 2011 at 12:40 pm

    #1270294

    If the column datatype is of character type

    STUFF(MyColumn, 6, 0, '-')

    If the column datatype is of integer type

    STUFF(CAST(MyColumn AS varchar(10)), 6, 0, '-')

  • January 6, 2011 at 1:24 pm

    #1270311

    Thanks actually I had a confusion.

    I have 2 tables. both have zipcode columns.

    In table a--> zipcode values are = 034563456 (9 digit values for all rows)

    In table b--> zipcode values are = 04567,07306-1234 (some values are 5 digits and some values are 5-4).

    I need to join both tables. Join on based on first 5 digits on zipcode values.

    1) So now I need to create one more column in table a which displays only first 5 digits. How can I seperate first 5 digits out of total 9 digits?

    2) For the second table b, same as above I need a different column that displays only 5 digits.

    Please let me know or give me some hints.

    Thanks

  • January 6, 2011 at 1:48 pm

    #1270313

    here's a single SQL with both examples:

    formatted zip, and jsut the first 5:

    select CASE

    WHEN LEN(ZIPCODE) > 5

    THEN STUFF(ZIPCODE,6,0,'-')

    ELSE ZIPCODE

    END AS FORMATTEDZIP,

    LEFT(ZIPCODE,5) AS ZIP5

    FROM YOURTABLE

    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!

  • January 6, 2011 at 3:11 pm

    #1270352

    You can join on:

    ON LEFT(A.ZipCode, 5) = LEFT(B.ZipCode, 5)

    (Performance may not good.)

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

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