0

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?

Edited by scaiferw

1
Contributor
1
Reply
5
Views
4 Years
Discussion Span
Last Post by scaiferw
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.