sql return and social

Tags:
SQL
brief question, i have to create a carrier return for a specific line in a file is this syntax correct; select a || b || c ||; also i have 2 sets of social security number which i am pulling from a table. both socials are brought in as lpad(ssn_id, 11, '00'), lpad(ssn2_id, 11, '00') now the first lpad brings back 00123456789. but the second lpad brings back 123-45-6789. how can i change the second lpad to come back as first, with no dashes and with 0 padding. I tried a like statement but it did not work. please help.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

I suspect that the first SSN is stored as a number, while the second is stored as a character string (ddd-dd-dddd) with hyphens inserted. There is nothing to pad in the second format, since it is already 11 characters long (and not a number anyway).

Hope this helps.

Phil

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Stevewaltz
    I am responding based on SQL for Oracle (7 or higher and probably all previous too). The SQL will work if you drop the last ||, otherwise you will get a missing expression error. As for the formatting of the SSNs, I suspect the columns are not formatted the same way, or worse yet, formatting is inconsistent in one or both columns. Check your data and see if ssn_id is consistently numbers (or numbers and spaces) and ssn2_id is consistently groups of 3, 2 and 4 separated by hyphens. If so, you need to use a format or function to remove the hyphens from ssn2_id. Otherwise, you'll need to identify the different storage formats and apply different different display formats accordingly.
    0 pointsBadges:
    report
  • Alex8809
    How do i modify the second ssn to a number?
    0 pointsBadges:
    report
  • Lukedavies
    The reason the 2nd LPAD doesn't work is that it is already 11 characters long, including the dashes. You need to translate the dashes into nothings and then do the LPAD.
    0 pointsBadges:
    report
  • FerencMantfeld
    This is straight forward enough. Oracle: select lpad(replace('123-456-789','-',''),11,'0') as TEST1 from dual; TEST1 ---------------------- 00123456789 Squeal Server: select ssn1, replicate('0',11-LEN(replace(ssn1,'-',''))) + replace(ssn1,'-','') as SSN2 from ssn_test ssn1 SSN2 123-45-6789 00123456789 hope that helps: Regards: Ferenc
    0 pointsBadges:
    report
  • Alex8809
    I tried the carriage return as follows and it gives me an error any suggestions; select a, b, c, d, f ||, i need a carriage return at a specific character spot, on space 181.
    0 pointsBadges:
    report
  • Holdemchamp
    In response to your first question try select a||b||c||chr(10) For Oracle, this will concatenate a carriage return at the end of the line.
    0 pointsBadges:
    report
  • Annbuck
    Since you are producing fixed length data and every column will need a lpad or rpad, try quoting the number of spaces you need to make up to 180 chars with CR on 181: select a||b||c||' '||chr(10) also I am sure you have already figured out that any number returned from the database in the format 00123456 is either stored as character data or has been passed thru the lpad and to_char functions.
    0 pointsBadges:
    report
  • Alex8809
    Thanks to all, my social and spacing are corrected now.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: