SQL - Selecting portion of a string

If I have a simple table where the data is such that the rows contains strings like:

/abc/123/gyh/tgf/345/6yh/5er

In SQL, how can I select out the data between the 5th and 6th slash? Every row I have is simply开发者_如何学JAVA data inside front-slashes, and I will only want to select all of the characters between slash 5 and 6.

CLR functions are more efficient in handling strings than T-SQL. Here is some info to get you started on writing a CLR user defined function.

• http://msdn.microsoft.com/en-us/library/ms189876.aspx
• http://www.mssqltips.com/tip.asp?tip=1344

I think you should create the function that has 3 parameters:

1. the value you are searching
2. the delimiter (in your case: /)
3. The instance you are looking for (in your case: 5)

Then you split on the delimiter (into an array). Then return the 5th item in the array (index 4)

Here is a t-sql solution, but I really believe that a CLR solution would be better.

DECLARE @RRR varchar(500)
SELECT @RRR = '/abc/123/gyh/tgf/345/6yh/5er'


DECLARE
    @index INT,
    @INSTANCES INT

SELECT
    @index = 1,
    @INSTANCES = 5

WHILE (@INSTANCES > 1) BEGIN
    SELECT @index = CHARINDEX('/', @RRR, @index + 1)
    SET @INSTANCES = @INSTANCES - 1
END

SELECT SUBSTRING(@RRR, @index + 1, CHARINDEX('/', @RRR, @index + 1) - @index - 1)

SELECT SUBSTRING(myfield,

  /* 5-th slash */
  CHARINDEX('/', myfield,
    CHARINDEX('/', myfield,
      CHARINDEX('/', myfield,
        CHARINDEX('/', myfield,
          CHARINDEX('/', myfield) + 1) + 1) + 1) + 1)
  + 1,

  /* 6-th slash */          
  CHARINDEX('/', myfield,
    CHARINDEX('/', myfield,
      CHARINDEX('/', myfield,
        CHARINDEX('/', myfield,
          CHARINDEX('/', myfield,
            CHARINDEX('/', myfield) + 1) + 1) + 1) + 1) + 1)
  -
  /* 5-th slash again */
  CHARINDEX('/', myfield,
    CHARINDEX('/', myfield,
      CHARINDEX('/', myfield,
        CHARINDEX('/', myfield,
          CHARINDEX('/', myfield) + 1) + 1) + 1) + 1)
  - 1)

FROM myTable
WHERE ...

This will work, but it's far from elegant. If possible, select the complete field and filter out the required value on the client side (using a more powerful programming language than T-SQL). As you can see, T-SQL was not designed to do this kind of stuff.

(Edit: I know the following does not apply to your situation but I'll keep it as a word of advise for others who read this:)

In fact, relational databases are not designed to work with string-separated lists of values at all, so an even better solution would be to split that field into separate fields in your table (or into a subtable, if the number of entries varies).

Maybe... SELECT FROM `table` WHERE `field` LIKE '%/345/%'