How to format certain types of data

[sasstuff]

Does anyone have code snippetts they'd be willing to share that format certain types of data, such as zipcodes, telephone numbers, SSNs and so on? I've looked on the web via Google but haven't seen anything relatively simple. Any help or sharing will be appreciated. Thak you in advance!

 

[gmmastros]

In my opinion, code snippets don't really work too well because it depends on how the data is stored to begin with. Also, formatting is usually best applied in a front end application (like an application or web page) instead of in the database itself. Usually front end apps are better at string manipulation (which is the ultimate goal of formatting).

If you must do formatting in SQL, then you should be aware of some of the string handling functions... particularly Left; Right; SubString; Replace; and Stuff.

Stuff is particularly handy for formatting because it's easy to insert characters in to strings. For example:

Declare @SSN VarCHar(20)

Set @SSN = '111223333'

Select Stuff(@SSN,6, 0, '-')
Select Stuff(Stuff(@SSN,6, 1, '-'),4,0,'-')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sasstuff]

Thanks for replying... we have a bunch of system analysts who are adverse to anyone doing things in "their" front ends, which is why I asked about possible code snippets. While STUFF looks interesting, we don't want to delete any characters in our data.We're only interested in formatting our data for reports. We're aware of the functions you mentioned, Left; Right; SubString; Replace and are looking to see if anyone has any efficient code snippets for formatting of output data on a report.

Thanks again.

 

[gmmastros]

Stuff doesn't have to delete anything. In the code I posted, I had an error that caused it to remove a character from the data. The corrected code is this:

Declare @SSN VarCHar(20)

Set @SSN = '111223333'

Select Stuff(@SSN,6, 0, '-')
Select @SSN As Before,
       Stuff(Stuff(@SSN,6, [!]0[/!], '-'),4,0,'-') As After

If you copy/paste this code to a query window and run it, you'll see that the only changes that are made are the insertion of the dashes.

I should also mention that the stuff function works well for "redacting" data. You know.... like when you order something on-line and the email you get says something like...

[tt]
Order #: 123-4567890-1234567
Shipping Method: Standard Shipping
Credit Card: ************1234
[/tt]

Declare @CreditCardNumber VarChar(16)
Set @CreditCardNumber = '1111222233331234'

Select Stuff(@CreditCardNumber, 1, 12, '************')

I don't know if any of this is useful or not. Without specifics, it's difficult to give good/appropriate advice.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sasstuff]

Thanks again!

Your information has been helpful. We have just started down the road of SQL (Oracle) and are discovering as we go.

We appreciate your help.

 

[SQLBill]

George's advice probably won't help if you are using Oracle. You need to post your questions in one of the Oracle forums as this forum is for Microsoft SQL Server. All SQL is not the same.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

we have a bunch of system analysts who are adverse to anyone doing things in "their" front ends

There's your problem. Tell your SAs that presentation logic belongs in the presentation layer, NOT the data layer.

Example: They want phone numbers to be formatted as (999)999-9999. So you place the string manipulation in your stored procedure. You find that the stored procedure can supply more than one application nicely, so you permit other apps to use the SP.

Now the original requestor comes along and says that the phone number must now look like 999-999-9999.

Hmm....

-------++NO CARRIER++-------