CODEWITHSUNDEEP
sqlsql-server
njj56
njj56

Reputation: 621

Issues writing query that locates a part of a string

I am trying to write a query that will search a list of strings in my database. All the strings are in the same format - below is an example of some of the strings 

LP-01-MG-01
LN-01-MG-02
LP-02-MG-01
LP-02-MG-04

Each dash represents a different part of the string. I need to find all the different results for the last number sequence of these strings. For example with the 4 listed above, would need to return 01, 02 and 04. I have been unable to do this with using the like command and underscores for individual character wildcards or a % at the end for anything after. Anyone have any suggestions? Thanks.

edit - all of the items have a few words after the number sequence as well, not sure if this will matter for any way anyone can solve this issue.

Upvotes: 0

Views: 41

Answers (2)

GarethD
GarethD

Reputation: 69819

DECLARE @S VARCHAR(100)
SET @S = 'LP-01-MG-01'

SELECT  RIGHT(@S, CHARINDEX('-', REVERSE(@S)) - 1)

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270775

If all of them have only two digits following the last hyphen, you can use a combination of the standard SQL RIGHT() and LEFT() function, available in most RDBMS packages.

SELECT RIGHT(LEFT(columnname, 11), 2) AS num FROM tbl;

If you don't want duplicates, use DISTINCT

SELECT DISTINCT RIGHT(LEFT(columnname, 11), 2) AS num FROM tbl;

Also possible, and perhaps clearer, with SUBSTRING()

SELECT DISTINCT SUBSTRING(columnname, 10, 2) AS num FROM tbl;

Upvotes: 2

Related Questions