SQL Server: Any Equivalent of Strpos()

SQL Server: any equivalent of strpos()?

User charindex:

Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6

Link

Join tables on a partially matched columns

You need to concatenate '%' with column T2.column and not with the string value 'T2.column':

like ('%' || T2.column)

or

like CONCAT('%', T2.column)

WHERE clause with strpos

"col001,col002" is a delimited identifier, i.e. the dbms thinks it's a column name. Use single quotes for string literals.

I.e.

select variable, col001, col002
from test
where strpos('col001,col002', variable) > 0

stripos equivalent in MySQL

You can use INSTR(str,substr):

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

SELECT INSTR('foobarbar', 'bar');
    -> 4
SELECT INSTR('xbar', 'foobar');
    -> 0

Difference between STRPOS and POSITION in postgresql

Those functions do the exactly same thing and differ only in syntax. Documentation for strpos() says:

Location of specified substring (same as position(substring in string), but note the
reversed argument order)

Reason why they both exist and differ only in syntax is that POSITION(str1 IN str2) is defined by ANSI SQL standard. If PostgreSQL had only strpos() it wouldn't be able to run ANSI SQL queries and scripts.

Is there any function available to find string position after specified index in postgresql

STRPOS accepts only two arguments. But you can chain a call to substring and easily achieve your objective.

SELECT STRPOS(SUBSTRING('my name is database',2), 'm')

Note that postgresql strings start from 1 and not 0 so you have to put 2 instead of 1 in the above query. Of course the result is relative to the new string 'y name is database' so you will have to add 1 to the result.

What is the CHARINDEX (SQL SERVER) equivalent in POSTGRESQL?

The equivalent function in postgresql is:

strpos(string, substring)

Or:

position(substring in string)

They are equivalent, just with different order in parameters.

If you also need parameter start_location, you will need to pass a substring to strpos.

You can find them in: https://www.postgresql.org/docs/current/functions-string.html

Multiple strpos possible?

How about:

if (preg_match('/\{.*?:.*?\}/', $string)) {
    echo 'string is good.';
}

Where:

/
  \{    : openning curly brace (must be escaped, it' a special char in regex)
  .*?   : 0 or more any char (non greeddy)
  :     : semicolon
  .*?   : 0 or more any char (non greeddy)
  \}    : closing curly brace (must be escaped, it' a special char in regex)
/

---

Related Topics