substring of variable length

问题:

I have a table with a column which contains strings like below.

RTSPP_LZ_AEN
RTSPP_LZ_CPS
RTSPP_LZ_HOUSTON
RTSPP_LZ_LCRA
RTSPP_LZ_NORTH
RTSPP_LZ_RAYBN
RTSPP_LZ_SOUTH
RTSPP_LZ_WEST
RTSPP_BTE_CC1 
RTSPP_BTE_PUN1 
RTSPP_BTE_PUN2

I need to get the substring from the second occurrence of _ till the end of string and as you can see the substring is not of fixed length. The first part is not always fixed it can change. As of now I am using the following code to achieve it.

SELECT SUBSTRING([String],CHARINDEX('_',[String],(CHARINDEX('_',[String])+1))+1,100)
FROM [Table]

As you can see I am taking an arbitrary large value as the length to take care of variable length. Is there a better way of doing it?

回答1:

You can use CHARINDEX in combination with REVERSE function to find last occurrence of _, and you can use RIGHT to get the specified number of characters from the end of string.

SELECT RIGHT([String],CHARINDEX('_',REVERSE([String]),0)-1)

SQLFiddle DEMO

回答2:

You can try giving len([string]) as the last argument :

 SELECT SUBSTRING([String],CHARINDEX('_',[String],(CHARINDEX('_',[String])+1))+1,len([string])) FROM [Table]

回答3:

You could use a common table expression to do the job like the code below. The gives the added flexibility to get all sub strings no matter how many underscores are in the string.

;WITH cte AS (
    SELECT 
      0 AS row
      ,CHARINDEX('_', [String]) pos
      ,[String] 
    FROM [Table]
    UNION ALL
    SELECT 
      row + 1
      ,CHARINDEX('_', [String], pos + 1)
      ,[String]
    FROM cte
    WHERE pos > 0
)
SELECT 
    row
    ,[String]
    ,pos
    ,SUBSTRING([String], pos + 1, LEN([String]) -pos)
FROM cte 
WHERE pos > 0
-- Remove line below to see all possible substrs
  AND row = 1
ORDER BY 
  [String], pos

回答4:

Or, even this:

SELECT SUBSTRING([String],CHARINDEX('_',[String],(CHARINDEX('_',[String])+1))+1,
             LEN([String])-CHARINDEX('_',[String])+1)