CODEWITHSUNDEEP
sql-server
sqluser
sqluser

Reputation: 403

select only integer from right before certain character

I want a simple solution to select only numbers from right of a VARCHAR column before a certain character.

For example, in the strings below, I only want to select numbers before slash character. The numbers vary, it can be 1 digit or more.

'ST/11/SCI/1' 'ST/11/SCI/22' 'ST/11/BIO/854' 'ST/11/BIO/5421'

Upvotes: 1

Views: 516

Answers (5)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use REVERSE with CHARINDEX so as to locate the position of the last '/' character. Then use RIGHT to extract the number:

DECLARE @str VARCHAR(100) = 'ST/11/BIO/854'
SELECT RIGHT(@str, CHARINDEX('/', REVERSE(@str)) - 1)

Edit:

To get second number starting from the end you can use:

DECLARE @str VARCHAR(100) = 'ST/11/BIO/1288/544'
SELECT SUBSTRING(@str, q2.x + 2, q2.x - q1.x - 1) 
FROM (SELECT @str AS v) AS t
CROSS APPLY (SELECT CHARINDEX('/', REVERSE(@str))) AS q1(x)
CROSS APPLY (SELECT CHARINDEX('/', REVERSE(@str), q1.x + 1)) AS q2(x)
CROSS APPLY (SELECT LEN(@str)) AS s(l)

Upvotes: 3

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I poste this as another answer, because the approach is completely different to the one of my other answer:

Starting with SQL Server 2012 (thx @NEER!) there is PARSENAME, which is a very straight approach to split a dot-delimited string up to 4 parts:

DECLARE @stringTable TABLE(string VARCHAR(100));
INSERT INTO @stringTable VALUES
 ('ST/11/SCI/1'),('ST/11/SCI/22'),('ST/11/BIO/854'),('ST/11/BIO/5421');

SELECT  PARSENAME(REPLACE(s.string,'/','.'),1)
FROM @stringTable AS s

The result

1
22
854
5421

Upvotes: 2

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use SUBSTRING, CHARINDEX, REVERSE

REVERSE : It will REVERSE your original string value

CHARINDEX : It will find index/location of any character from string value

SUBSTRING : It will split your string value

DECLARE @myString as VARCHAR(50)='ST/11/SCI/22'
SELECT 
    REVERSE
    (
        SUBSTRING
        (
            REVERSE(@myString),
            0,
            CHARINDEX('/',REVERSE(@myString))
        )
    )

Upvotes: 2

Ayak973
Ayak973

Reputation: 454

You can use a combination of reverse and charindex :

select reverse(left(reverse(...), charindex('/', reverse(...)) -1))

Documentation of reverse : https://msdn.microsoft.com/fr-fr/library/ms180040.aspx

Documentation of charindex : https://msdn.microsoft.com/fr-fr/library/ms186323.aspx

Test: select reverse(left(reverse('ST/11/SCI/2'), charindex('/', reverse('ST/11/SCI/2')) -1))

Output: 2

Note: We didnt know your version of SQL-Server, check the documentation of reverse/charindex to know wich version of SQL Server is supported !

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If you need all data you can split this easily and type-safe: 

DECLARE @stringTable TABLE(string VARCHAR(100));
INSERT INTO @stringTable VALUES
 ('ST/11/SCI/1'),('ST/11/SCI/22'),('ST/11/BIO/854'),('ST/11/BIO/5421');

SELECT s.string
      ,x.value('/x[1]','nvarchar(10)') AS Part1
      ,x.value('/x[2]','int') AS Part2
      ,x.value('/x[3]','nvarchar(10)') AS Part3
      ,x.value('/x[4]','int') AS Part4
FROM @stringTable AS s
CROSS APPLY(SELECT CAST('<x>' + REPLACE(s.string,'/','</x><x>')+'</x>' AS XML)) AS A(x)

The result

string          Part1   Part2   Part3   Part4
ST/11/SCI/1     ST      11      SCI     1
ST/11/SCI/22    ST      11      SCI     22
ST/11/BIO/854   ST      11      BIO     854
ST/11/BIO/5421  ST      11      BIO     5421

Upvotes: 0

Related Questions