I have a series of user records in the form "Lname, Fname (ABCD)" where ABCD is the division the user works in.

I need to be able to extract that substring so I can sort on it, count etc to I can see what divisions are using resources.

In MySQL I'd add something like this to my select statement: 'substring(name,-5,4) AS division' to capture this and allow me to sort, count, etc.

On SQLSERVER I can't figure it out.

Can anyone help me?

Figured it out.

DECLARE @first_char nvarchar(10)
DECLARE @second_char nvarchar(10)

SET @first_char = '(';
SET @second_char = ')';

SELECT 
    name,
    SUBSTRING (
        -- column
        name
        -- start position
        ,CHARINDEX(@first_char, name , 1) + 1
        -- length
        ,CASE
        WHEN (CHARINDEX(@second_char, name , 0) - CHARINDEX(@first_char, name, 0)) > 0
        THEN CHARINDEX(@second_char, name, 0) - CHARINDEX(@first_char, name, 0) - 1
        ELSE 0
        END
    ) AS "section",
    login,email,active
    FROM db.dbo.table 
    WHERE name like '%(%)%'
    AND active = 'True'
    ORDER BY "section"

Hope this is of use to someone else.

Rob

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.