Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Extract everything after a certain character

Posted on 2011-04-27
10
Medium Priority
?
371 Views
Last Modified: 2012-06-22
I have a table that has many rows of data like the following ...
abcdefg_524_test_testtest_32
4_abcdefg_test_testtest_32
abcdefg_test_testtest_382
abcdefg_test_tes_ttest_32

I need to get the set of numbers after the last "_" in the string.  How can this be done?


0
Comment
Question by:H-SC
  • 5
  • 3
  • 2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35475791
select substring( yourfield, len(yourfield) - charindex('_', reverse(yourfield))  - 1, len(yourfield)
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35475827

select REVERSE(LEFT(REVERSE(Col), CHARINDEX('_', REVERSE(Col), 1)-1))
0
 
LVL 1

Author Comment

by:H-SC
ID: 35476224
angelIII,

This may work for most of the data, but for some reason a string like : abcdefg_test_testtest_382
 brings back "82"

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:H-SC
ID: 35476243
ewanqoya,

I ran your sql and keep getting the error :
Invalid length parameter passed to the SUBSTRING function.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35476337
sorry, + 1 and not -1

select substring( yourfield, len(yourfield) - charindex('_', reverse(yourfield)) + 1, len(yourfield)
0
 
LVL 1

Author Comment

by:H-SC
ID: 35476406
angelIII,

got it, many thanks works great
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35477636

H-SC
I dont use SUBSTRING in my solution

SELECT REVERSE(LEFT(REVERSE(Col), CHARINDEX('_', REVERSE(Col), 1)-1))
0
 
LVL 1

Author Comment

by:H-SC
ID: 35478066
ewanqoya,
hmm, not sure but I indeed still get that exception.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35478107

Very interesting

But angelIII's solution works for you already
0
 
LVL 1

Author Comment

by:H-SC
ID: 35478170
ewanqoya,

I know, I just like to follow up with everyone who responds to the post out of respect.  many thanks for your help here as well.  take care :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question