SQL - Selecting portion of a string

Question

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 data inside front-slashes, and I will only want to select all of the characters between slash 5 and 6.

Answer 1

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

Answer 2

I think he/she may mean that they are looking to retrieve all data within the 5th and 6th brackets, in this case 345 but it may be different for each field within your column.

Ode, am I right?

Answer 3

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).

Answer 4

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)