CODEWITHSUNDEEP
sqlt-sqlsubstring
Si8
Si8

Reputation: 9225

How to get certain word from a column value

Column A

/Site/Test1/mysite/Do?id=90
/Site/Test2/mysite/Done?id=10
/NewSite/Site/Test3/mysite/Do?id=90
/Site/Test3/mysite/Done?id=1901

What I am trying to do is get the Test# from each row as well as the # after the =.

I tried the following:

Select 
    SUBSTRING(Column A, CHARINDEX('/', Column A, 1) + 7, LEN(Column A)),
    SUBSTRING(Column A, CHARINDEX('=', Column A, 1) + 1, LEN(Column A)), 
    Column A
from
    Table1

I am able to get the # after the = but how can I get the Test# from each row.

UPDATE: Test# is an example, it can be anything in there. What is for certain is Site and NewSite.

UPDATE #2:

Updated Table:

Column A
/Site/My%20Web%20Site/mysite/Do?id=90
/Site/Test%20It%20Out/mysite/Do?id=101
/Site/Test1/dummy/Done?id=1000
/NewSite/Site/No%20Way/thesite/Do?id=909

Result:

Col1                Col2
My%20Web%20Site     90
Test%20It%20Out     101
Test1               1000
No%20Way            909

Upvotes: 1

Views: 52

Answers (2)

SqlZim
SqlZim

Reputation: 38023

select 
    Col1 = substring(a
      , charindex('/Site/', a)+6
      , charindex('/', a,(charindex('/Site/', a)+6))-(charindex('/Site/', a)+6)
      ) 
  , Col2 = substring(a
      , charindex('=', a, 1) + 1
      , len(a))
from t

rextester demo: http://rextester.com/DEBB37305

returns: 

+-----------------+------+
|      Col1       | Col2 |
+-----------------+------+
| My%20Web%20Site |   90 |
| Test%20It%20Out |  101 |
| Test1           | 1000 |
| No%20Way        |  909 |
+-----------------+------+

Upvotes: 1

GandRalph
GandRalph

Reputation: 635

This should work:

select SUBSTRING(col,CHARINDEX('Test',col),5)

To test it with one example:

select SUBSTRING('/Site/Test1/mysite/Do?id=90',CHARINDEX('Test','/Site/Test1/mysite/Do?id=90'),5)

Upvotes: 0

Related Questions