SQL substring with charIndex

Question

I want to select a part of a string in two query

"Server \ Windows \ Access Database"
"Access Database \ Server \ Windows"

i want to select whatever is there after the first '\' in one query and in second query i want to select the text after second '\'

between the '\' the length of the text keeps changing.

I have tried:

SELECT Somecolumn=Substring(column1, 0, Charindex('\', column1, 1)) 
FROM   dbo.sometable

The result:

SOMECOLUMN
Server
Access Database

Sql-Fiddle: http://sqlfiddle.com/#!3/f0458/1

@Tim am using this query select Somecolumn=substring(column1,0,charindex('\',column1,1)) from table in on query i want "Access Databae" as a result and in another i want "Windows" — NoviceToProgramming
– NoviceToProgramming, Commented Sep 24, 2013 at 7:27
So what is your desired result? Edit your question accordingly. — Tim Schmelter
– Tim Schmelter, Commented Sep 24, 2013 at 7:33
What do you mean by two queries? and your expected results are completely different to the description. — Indi
– Indi, Commented Sep 24, 2013 at 7:39
my actual query is select CaseType = RTRIM(LTRIM(SUBSTRING(Description,0,CHARINDEX('\',Description,1)))) from mytable by runnig this am getting 'server' as the output in short befoe first '\'. i want the output by using two diffrent query in first i want the output after first '\' and in second i want the ouput after second '\' — NoviceToProgramming
– NoviceToProgramming, Commented Sep 24, 2013 at 7:42

Ram Singh · Accepted Answer · 2013-09-24 07:30:32Z

2

To get the string after first "\" you can use the below query:

declare @residence varchar(200)
set @residence='Server \ Windows \ Access Database'
select left(@residence, CHARINDEX('\',@residence)-1) AS [Result]

and for second : you want to get the string after second "\": for this you can use below query

select RIGHT(@residence, CHARINDEX('\', REVERSE('\' + @residence)) - 1) AS [Result]

thats all

answered Sep 24, 2013 at 7:30

Ram Singh

6,97235 gold badges107 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Indi · Accepted Answer · 2013-09-24 07:42:26Z

Try this:

create table t (val varchar(50))

insert into t
select 'Server \ Windows \ Access Database'
union all
select 'Access Database \ Server \ Windows'

Fiddle demo:

;with cte as (

  select charindex('\',val,0) index1,
              charindex('\',val, charindex('\',val,0)+1) index2
  from t        
)
select val, right(val, len(val) - index1) first,
            right(val, len(val) - index2) second
from cte

|                      FIRST |           SECOND |
-----------------------------|------------------|--
|  Windows \ Access Database |  Access Database |
|           Server \ Windows |          Windows |

Collectives™ on Stack Overflow

SQL substring with charIndex

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related