CODEWITHSUNDEEP
sql-server-2008
NoviceToProgramming
NoviceToProgramming

Reputation: 103

SQL substring with charIndex

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

  1. "Server \ Windows \ Access Database"
  2. "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

Upvotes: 2

Views: 6529

Answers (2)

Kaf
Kaf

Reputation: 33809

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 |

Upvotes: 1

Ram Singh
Ram Singh

Reputation: 6918

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

Upvotes: 2

Related Questions