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

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

Splitting a field in SQL

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
CMChalcraft
Asked:
CMChalcraft
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
What you need as the output from the string, I shall write it for you
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ste5anSenior DeveloperCommented:
E.g.
DECLARE @Text NVARCHAR(255) = N'BPL-TS02/COMPANY-NAME\jsmith';

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

Open in new window

1
 
Scott PletcherSenior DBACommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
There are good solutions here, so should be closed by accepting the Experts comments.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pawan, even your comment is right, ste5an's and Scott's also are.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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