?
Solved

INSERT INTO SQL QUERY FIELD

Posted on 2013-12-02
5
Medium Priority
?
390 Views
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 -
0
Comment
Question by:teogos
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

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

SELECT @Data

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

Open in new window

0
 
LVL 32

Accepted Solution

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

Expert Comment

by:awking00
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)
0
 
LVL 32

Expert Comment

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

Expert Comment

by:Tony303
ID: 39691100
@awking.

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 :-)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 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