Query for Substring Formation

query for substring formation

This is one of those examples of how there's similar functionality between SQL and the various extensions, but are just different enough that you can not guarantee portability between all databases.

The SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99. Why this took them so long, I don't know.

The crux of your need is to obtain a substring of the existing column value, so you need to know what the database substring function(s) are called.

Oracle

---

SELECT SUBSTR('abcd_01', -2) FROM DUAL

Oracle doesn't have a RIGHT function, with is really just a wrapper for the substring function anyway. But Oracle's SUBSTR does allow you to specify a negative number in order to process the string in reverse (end towards the start).

SQL Server

---

Two options - SUBSTRING, and RIGHT:

SELECT SUBSTRING('abcd_01', LEN('abcd_01') - 1, 2)
SELECT RIGHT('abcd_01', 2)

For brevity, RIGHT is ideal. But for portability, SUBSTRING is a better choice...

MySQL

---

Like SQL Server, three options - SUBSTR, SUBSTRING, and RIGHT:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)
SELECT SUBSTRING('abcd_01', LENGTH('abcd_01') - 1, 2)
SELECT RIGHT('abcd_01', 2)

PostgreSQL

---

PostgreSQL only has SUBSTRING:

 SELECT SUBSTRING('abcd_01' FROM LENGTH('abcd_01')-1 for 2)

...but it does support limited pattern matching, which you can see is not supported elsewhere.

SQLite

---

SQLite only supports SUBSTR:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)

Conclusion

---

Use RIGHT if it's available, while SUBSTR/SUBSTRING would be better if there's a need to port the query to other databases so it's explicit to others what is happening and should be easier to find equivalent functionality.

Query only records that match specific substring - Laravel 5

Did you try with 'LIKE'

$visitors = Visitor::orderBy('created_at', 'desc')
->where('os', 'like', '%bot%') //<----- Match any word containing ...bot.. at any place
->get();

Using a variable

$visitors = Visitor::orderBy('created_at', 'desc')
->where('os', 'like', '%'. $bot .'%') //<----- Match the searched variable
->get();

Select substring from a column in SQL

The syntax is like this : SUBSTRING ( expression ,start , length )

For example :

SELECT x = SUBSTRING('abcdef', 2, 3);

Here is the result set:

x
----------
bcd

You should do like this :

select count(*) as count from 
table 1 join table 2
on substring(value,20,12)=substring(code,0,12)

How to select/filter against substring in a list of strings?

If I understood correctly, you want to obtain the top 20 activities, sorted by ID, whose Referrer field do not contain any of the items in MyDomains as a substring.

The following, or something similar, should work:

var theActivities = (from a in dc.Activities
    where a.Referrer != null
        && a.Referrer.Trim().Length > 0
        && a.SearchResults.Count() == 0
    select a);

foreach(var domain in MyDomains) {
    theActivities = theActivities.Except(dc.Activities.Where(a => a.Referrer.Contains(domain)));
}

theActivities = theActivities.OrderBy(a => a.Id).Take(20);

//Now you can query theActivities

Note however that you will end up with a rather long SQL query, since a WHERE clause will be added for each item in MyDomains.

UPDATE: Indeed, this will not work. Since the query expression is evaluated when it is actually queried, all the Except clauses use the same value for the domain variable (which is the last value set).

The only solution I can think then, is to dinamically generate a SQL command to get the data. I have no way to validate code right now, but it would be approximately something like this:

var whereClauses=new List<string>();

for(int i=0; i<MyDomains.Length; i++) {
    whereClauses.Add(string.Format("(Referrer like {{{0}}})", i));
}

var sqlFormattedDomains=MyDomains.Select(d => string.Format("%{0}%", d)).ToArray();

var sqlCommand=string.Format(
    "select top 20 * from Activities where (not Referrer is null) and (not ({0})) order by Id",
    sqlFormattedDomains.Join(" or "));

var x=dc.ExecuteQuery<Activities>(sqlCommand, sqlFormattedDomains);

You will have to expand the SQL command for the SearchResults.Count() == 0condition, I guess that this has to do with adding a join clause to another table.

SQL: Order By, Substring, Union

You need to do another JOIN to the db table to get the Version for each [KEY] sort by that:

SELECT 
    e.[GUID], e.[KEY], e.[VALUE] 
FROM db e 
INNER JOIN db g 
    ON  e.[GUID] = g.[GUID] 
        AND g.[Key] = 'Session.Type'
        AND g.[Value] IN ('EndMatchTypeA', 'EndMatchTypeB')
INNER JOIN (
    SELECT [GUID], [KEY], [VALUE]
    FROM db
    WHERE
        [KEY] = 'Version'
)v
    ON e.[GUID] = v.[GUID]
WHERE 
    e.[KEY] IN ('CharacterID', 'OpponentID', 'Version')
ORDER BY 
    SUBSTRING(v.[VALUE], 1, 8);

---

You can also use CROSS APPLY

SELECT 
    e.[GUID], e.[KEY], e.[VALUE] 
FROM db e 
INNER JOIN db g 
    ON  e.[GUID] = g.[GUID] 
        AND g.[Key] = 'Session.Type'
        AND g.[Value] IN ('EndMatchTypeA', 'EndMatchTypeB')
CROSS APPLY (
    SELECT [GUID], [KEY], [VALUE]
    FROM db
    WHERE
        [KEY] = 'Version'
        AND [GUID] = e.[GUID]
)v
WHERE 
    e.[KEY] IN ('CharacterID', 'OpponentID', 'Version')
ORDER BY 
    SUBSTRING(v.[VALUE], 1, 8);

Credits to Gordon for getting rid of the UNION ALL.

SQL query split long string formed by pairs

You can split up each row by first replacing ), with | then splitting on |.

We can then use CHARINDEX to find the remaining ,, and with some more trimming we can get the x and y

SELECT
    t.LineID,
    PointID = ROW_NUMBER() OVER (PARTITION BY t.LineID ORDER BY (SELECT NULL)),
    x = RTRIM(LEFT(v1.value, v2.comma - 1)),
    y = LTRIM(SUBSTRING(v1.value, v2.comma + 1, LEN(v1.value)))
FROM YourTable t
CROSS APPLY STRING_SPLIT(REPLACE(t.PointAxis, '),', '|'), '|') s
CROSS APPLY (VALUES (
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(s.value, '[', ''),
          ']', ''),
        '(', ''),
      ')', ''),
    ' ', '')
) ) v1(value)
CROSS APPLY (VALUES (
    CHARINDEX(',', v1.value)
) ) v2(comma)
WHERE v2.comma <> 0;

db<>fiddle.uk

---

Related Topics