Search code examples
sqlintersystems-cache

Distinct substring from nested select only if delimiter exists?

Here is the query I am using to parse certain pieces of a username from a field

SELECT SUBSTRING(UserName, CHARINDEX('^', UserName) + Len('^'),LEN(UserName) as un, 
FROM (
        SELECT TOP 10000 * 
        FROM SomeTable
        ORDER BY LocalDateTime DESC
)

The problem is that usernames which do not have a ^ are irrelevant and I do not want to include them. How can I do this? For instance, if I had the following data:

system1^name1
system2^name2
system3^name2
name3

I would receive these results:

name1
name2

NOTE: I do not want to modify the internal query.

Solution

  • I think you can just add a condition in the subquery or the outer query:

    SELECT SUBSTRING(UserName, CHARINDEX('^', UserName) + Len('^'), LEN(UserName) as un
FROM (SELECT TOP 10000 * 
      FROM SomeTable
      ORDER BY LocalDateTime DESC
     ) t
WHERE UserName LIKE '%^%'