CODEWITHSUNDEEP
sqlextract
Lucas Dresl
Lucas Dresl

Reputation: 1170

Substringing from a text a number

From the following string value of session i will like to keep with only the part when the first number big or the last | beginning

session
nea|fact|za|ninja|web|14ff95092e3x1d214cd2
nea|fact|za|ninja|web|15001274f5ex323c9f96
nea|fact|za|ninja|web|1502897832ax418ecf1a
nea|fact|za|ninja|web|150399c1418x215f0e52
nea|fact|za|ninja|web|1503b3cdf02x386fc450
ta|fact|za|ninja|web|1503b3cdf02x386fc450
ta|fact|za|ninja|web|1503b3cdf02x386fc450

expected result

14ff95092e3x1d214cd2
15001274f5ex323c9f96
1502897832ax418ecf1a
150399c1418x215f0e52
1503b3cdf02x386fc450
1503b3cdf02x386fc450
1503b3cdf02x386fc450

Upvotes: 0

Views: 43

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

if your db mysql then SUBSTRING_INDEX will help you

select SUBSTRING_INDEX(session, "|", -1);

Example:

select SUBSTRING_INDEX('nea|fact|za|ninja|web|14ff95092e3x1d214cd2', "|", -1);
returned: 14ff95092e3x1d214cd2

In mysql string related function

For Sql server your query will be

SELECT RIGHT(session , CHARINDEX ('|' ,REVERSE(session))-1)

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

In Oracle - 

SELECT SUBSTR(session, '|', -1)
FROM TABLE_NAME;

Upvotes: 1

Ramji
Ramji

Reputation: 385

For sql server:

declare @test varchar(1000) = 'nea|fact|za|ninja|web|14ff95092e3x1d214cd2'

SELECT RIGHT(@test , CHARINDEX ('|' ,REVERSE(@test))-1)

Upvotes: 1

Related Questions