asked on

Format Data Field - SQL

Hello Experts,

Can you provide me with the syntax for adjusting the display of a database field? It will be added as a column in a SQL view.

The fields is an integer. The final adjustment will be to convert it to a varchar. The 4 adjustment steps are:
- remove first character
- return characters 2 and 3
- append a dash '-'
- remove leading zeros from the rest of the integer (characters after the 3rd character) and append to the above

Examples: 11700109 would become 17-109

Thank you for your help,
~Chop

Ryan Chong

what's the database you're referring to?

Ryan Chong

can you also provide original values that contains 2 and 3?

and:

- append a dash '-'

where you want to insert this dash to ?

Chopp

ASKER

The database is being managed in the SQL management studio - the script I am adding the column to, is an SQL script.
The dash goes after the 3rd character - see the example below.
Examples: 11700109 would become 17-109

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chopp

ASKER

Thank you very much, Mike! I will try out your method today and get back to you (hopefully I won't lose electricity again). Below is a function that a friend made for me which works, but I was hoping to avoid creating another object. I was hoping to manage the display directly in the view.

Create Function [dbo][fn_FormatCaseNum] (@CaseNumberInt Int)
Returns VarChar(20)
As

Begin
Declare @CaseNumberString VarChar(20)
Declare @CaseNumberConverted VarChar(20)
Declare @LenOfCase int

Select @CaseNumberString = Cast(@CaseNumberInt As VarChar(20))
Set @CaseNumberConverted = ''
Set @LenOfCase = Len(@CaseNumberInt)

Select @CaseNumberConverted = Substring(@CaseNumberString, 2, 2) + '-'
Select @CaseNumberConverted = @CaseNumberConverted + ( Cast ( Cast( Substring(@CaseNumberString, 4, @LenOfCase) As Int) As VarChar(20)))

Return @CaseNumberConverted

End

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
In the view I call the function:

select distinct
dbo.fn_FormatCaseNum (Cases.CASEN) As CaseNumberDisplay

Chopp

ASKER

Hi Mike, is there a way to use your method in a View using the table.field directly instead of declaring a variable? Can the line below be somehow adjusted so that it would run?

, substring(varchar Cases.CASEN,2,2) + '-' + cast(cast(substring(varchar Cases.CASEN 4,20) as int) as varchar (20) ) as CaseNumberDisplay

Sharath S

try like this

SELECT SUBSTRING(CONVERT(VARCHAR(50),Cases.CASEN),2,2) + '-'
       CONVERT(VARCHAR(50),CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(50),Cases.CASEN),4,100)))
  FROM Cases

Open in new window

Chopp

ASKER

Thank you, Sharath. I tried your example but it threw the following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CONVERT'

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chopp

ASKER

Thank you very much, Sharath! That did work. It is exactly what I needed.
Have a splendid weekend!
Respectfully, Linda

Chopp

ASKER

Thank you, Sharath and Mike!