Extract data from a SQL varchar column...

Question

5.00/5 (1 vote)

See more:

I have a SQL table with multiple columns
Column [INI"] is a "varchar" that I would like to extract data from
Here is a sample [INI] value
\\ngic.com\fs\APPS\NPS\SQA\standard\XMLs\FL\FL IN MC\FL IN MC 01.xml
How can I extract the data following the last '\'?

SQL

SELECT TOP (1000) tests.id
      ,[INI]
      ,[App]
      ,[SubApp]
      ,[Environment]
      ,[Comment]
      ,[Heartbeat]
      ,[UpdatedBy]
      ,[Timestamp]
  FROM [QATestData].[dbo].[Tests] tests

What I have tried:

I have tried

(select value from STRING_SPLIT(ini,'\') ) as testcasename

Posted 18-Jul-18 5:26am

lovinlife045

Updated 18-Jul-18 6:53am

CHill60

v2

Add a Solution

Comments

CHill60 18-Jul-18 11:43am

We can't use your link. Post a sample of the file here

lovinlife045 18-Jul-18 12:06pm

Sorry for the confusion. That's not a link.
It is the data in the [INI] column

Here is a sample of the data in the [INI] column:
\\ngic.com\fs\APPS\NPS\SQA\standard\XMLs\FL\FL IN MC\FL IN MC 01.xml

I wish to extract all of the data following the last '\', in this case
"FL IN MC 01.XML"

Thank you

CHill60 19-Jul-18 4:11am

:laugh: Sorry about the misunderstanding. Glad you got a solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

littleGreenDude · Accepted Answer · 2018-07-18T06:54:00

Solution 2

Assuming SQL server, you could do the following:

select REVERSE(SUBSTRING(REVERSE(INI), 1, CHARINDEX('\', REVERSE(INI))-1))
FROM [QATestData].[dbo].[Tests] tests

Posted 18-Jul-18 6:54am

littleGreenDude

Comments

lovinlife045 18-Jul-18 13:28pm

Thank you. Your solution worked !!!

MadMyche · Accepted Answer · 2018-07-18T05:53:00

You will need to use a split function to basically create an array, and then follow that up by getting the value for the last element in the array.

I cannot give you specific code for this, as you did not provide which type of SQL you are using(MySql, Sql Server, et al). Also, not all versions of any particular type have that function built-in (eg Sql Server did not support prior to 2016)