Formatting varchar data into a certain format.

Question

I need to format data in a sql column that is currently entered like this:

Z04000002003.7

The desired output of this would be:

Z04/000/002/003.7

Every time a user enters data like this Z04000002003.7. The next time the user opens the record it would have automatically formatted it to display Z04/000/002/003.7.

Answer 1

When you say 'open the record' where exactly is that happening? A web page?

Do the formatting as close to the user as you can - UI layer. I don't think this is a SQL problem.

Answer 2

A couple of options:
1. Script update all the rows format from old to new standard
2. Like n8wrl said format on insert
3. Format on data return.

Answer 3

If you want to insert the slashes on INSERT or UPDATE (when the string gets into the database) or SELECT, you can do that in TSQL with a somewhat clumsy string expression:

SUBSTRING(thestring, 1, 3) + '/' + SUBSTRING(thestring, 4, 6) + '/' + ...

and so on, but I agree with other respondents that it may be a better architecture to perform such transformations "closer to the user" (in the UI, or perhaps in a business logic layer if those slashes are in fact part of the business logic, but UI looks likelier).

Answer 4

The data should be formatted on entry into the database. THe UI doesn't have to send exactly what was typed to the database. The user doesn't need to know you added the /s. If you can't change the data entry in the UI (which would be my first choice of where to change it), then write a trigger to process the data on insert or update to the correct format. Make sure that trigger can handle multitple row inserts or updates!