Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

How to split a field into two columns in a Query

I want to be able to via a query parse a single column  Claim No (123456789-001) into two separate fields in the resulting worksheet.

So the first 9 characters and then the number to the right of the "-"
0
seamus9909
Asked:
seamus9909
1 Solution
 
PatHartmanCommented:
You need to use the left() and right() functions

Select Left([Claim No], 9) as ClaimNo, Right([Claim No], 3) as ClaimSuffix From YourTable
0
 
seamus9909Author Commented:
Its telling syntax is not correct.  I am using Query Design and not the SQL itself.

am I missing the quote or bracket?
0
 
Rey Obrero (Capricorn1)Commented:
<I am using Query Design and not the SQL itself.>

post an image of your query
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Pawan KumarDatabase ExpertCommented:
Pls try.. Also check the name of the column - [Claim No].

1.
left(x,instr([Claim No],"-")-1)
trim(mid(([Claim No],instr(([Claim No],"-")+1))

2.
trim(split(([Claim No],"-")(0))
trim(split(([Claim No],"-")(1))
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
In Query Design, write into the Field Option
LeftField: Left([Claim No],9)

Open in new window

This is the first field that will be selected with Alias LeftField (of course you can use your preferred name)
Then the other field will be
RightField: Right([Claim No],3)

Open in new window

Example
0
 
seamus9909Author Commented:
Thank you so much!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now