Posted on 2013-12-02
Last Modified: 2013-12-03
Data is coming from the SLQ database like this B1234DPRF . I want to modify this on a query
to show me like B-1234-DPRF      ,any ideas how to do this  so. after the first character insert a -   then after the fifth character insert another -
Question by:teogos
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 12

Expert Comment

ID: 39691046
DECLARE @data as varchar(10)
SET @data = 'B1234DPRF'


SELECT left(@data,1) + '-' + Substring(@data,2,4) + '-' + SUBSTRING(@Data,6,4) AS Done

Open in new window

LVL 32

Accepted Solution

awking00 earned 500 total points
ID: 39691054
If always the second and seventh -
select substring(field,1,1) + '-' + substring(field,2,4) + '-' + substring(field,6,len(field))
LVL 32

Expert Comment

ID: 39691060
p.s. If always 4 characters after the last '-' then just substitute 4 for len(field).

You can also use left and right, I believe -
select left(field,4) +'-' substring(field(2,4) + '-' right(field,4)
LVL 32

Expert Comment

ID: 39691063
Tony303, sorry I never saw your post before I submitted.
LVL 12

Expert Comment

ID: 39691100

Yeah, it is rare that I get a solution in first!!
Normally, while I am working the question out there are bigger brains on the job and get in before me!!!

Cheers now.
T :-)

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Sever Import/export problem 7 51
How come this XML node is not read? 3 31
SQL Syntax 6 41
TSQL - How to get output results in row with additional character(comma) 7 16
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question