Solved

Splitting  a field in SQL

Posted on 2016-09-16
9
34 Views
Last Modified: 2016-10-09
I have this string BPL-TS02/COMPANY-NAME\jsmith.

I do I just extract the jsmith element? I cannot just use RIGHT as the name will vary in the number of characters.
0
Comment
Question by:CMChalcraft
9 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801115
What you need as the output from the string, I shall write it for you
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points (awarded by participants)
ID: 41801117
Please try this

---

DECLARE @ AS VARCHAR(1000) = 'BPL-TS02/COMPANY-NAME\jsmith'
SELECT SUBSTRING(@,CHARINDEX('\',@,0)+1,(DATALENGTH(@)+1)-22)

--

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41801118
Please try this

--

DECLARE @ AS VARCHAR(1000) = 'BPL-TS02/COMPANY-NAME\jsmith'
SELECT SUBSTRING(@,CHARINDEX('\',@,0)+1,(DATALENGTH(@)+1)-CHARINDEX('\',@,0))

--

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 125 total points (awarded by participants)
ID: 41801119
E.g.
DECLARE @Text NVARCHAR(255) = N'BPL-TS02/COMPANY-NAME\jsmith';

SELECT SUBSTRING(@Text, CHARINDEX('\', @Text) + 1, 255)

Open in new window

1
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points (awarded by participants)
ID: 41801644
RIGHT is easiest as long as the delimiter before the final string is always a "\" (or is always known to be a certain char(s)):

SELECT string, RIGHT(string, CHARINDEX('\', REVERSE(string)) - 1)
FROM (
    VALUES('BPL-TS02/COMPANY-NAME\jsmith'),
                  ('BPL-TS02/COMPANY-NAME\a'),
                  ('BPL-TS02/COMPANY-NAME\averyveryveryveryverylongname')
) AS sample_data(string)
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41827794
There are good solutions here, so should be closed by accepting the Experts comments.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41827796
Pawan, even your comment is right, ste5an's and Scott's also are.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

856 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