Sqream Db V1.9.6 Cheat Sheet
ADVERTISEMENT
1SQream DB v1.9.6 Cheat Sheet - STRINGS
Types
Modifications
Search and Matching
Patterns
(cont.)
VARCHAR(n) – ASCII string of maximum length
Pattern matching
Concatenation
Where
x is 'SQream DB v1.9.6' (VARCHAR)
and
y is 'DB' (VARCHAR)
Syntax
Description
NVARCHAR(n) – UTF-8 string of maximum length
'SQream ' || 'DB'
'SQreamDB'
match zero or more characters
NVARCHAR can’t be aggregated in
or
%
GROUP BY
'SQream' || ' DB'
CHARINDEX(needle,haystack[, start index])
used as a join key in this SQream DB version
match exactly one character
_
Returns the first character index, or 0 if not found
'SQream DB'
match any character between A and Z
[A‐Z]
Example:
'Value:' || (42 :: varchar(1))
Conversions
inclusive
CHARINDEX(y,x,1)
'Value*'
match any character not between A
[^A‐Z]
0
'Value:' || (42 :: varchar(2))
and Z
CAST(9 as varchar(2))
'Value:42'
match any one of a b and S
[abS]
'9'
Count patterns matching regex. Syntax:
Various
match any character that isn’t one of d
[^de]
true :: varchar(5)
REGEXP_COUNT(column, regex [,start index])
SUBSTRING(column,start position,length)
and e
'true'
Example:
match a b or
Examples:
[abC‐F]
CAST('1997‐01‐01' as datetime)
between C and F
REGEXP_COUNT(x,'[0‐9]')
SUBSTRING('SQream DB',1,3)
1997‐01‐01 00:00:00.000
3
'SQr'
2015 :: varchar(5)
Regex pattern matching
REGEXP_COUNT(x,'[0‐9]',13)
SUBSTRING('SQream DB',0,3)
'2015'
2
Syntax
Description
'SQ'
CAST('2015' as varchar(2))
Match beginning of a string
UPPER('SQream DB') 'SQREAM DB'
^
SQream DB can’t convert between
and
NVARCHAR
Find first occurrence of regex pattern. Syntax:
Match end of a string
$
LOWER('SQream DB') 'sqream db'
with casts in this version.
VARCHAR
Match any character
.
REGEXP_INSTR(column, regex [,start index])
REVERSE('Meow') 'Woem'
Match the previous pattern 0+ times
*
Example:
RTRIM(' Quack ') ' Quack'
Match the previous pattern 1+ times
+
String literals
REGEXP_INSTR(x,'[0‐9]')
LTRIM(' Quack ') 'Quack'
Match the previous pattern 0 or 1
?
12
times
Strings literals must be surrounded by a
Where
x is 'Flüßigkeit' (NVARCHAR):
Match either 'de' or 'abc'
de|abc
single quote (')
Extract substring of regex pattern match or
if
''
LEFT(x,6) 'Flüßig'
Match 0+ instances of the sequence
(abc)*
Strings containing single quotes must be escaped by
not found. Syntax:
RIGHT(x,4) 'keit'
'abc'
writing two adjacent single quotes:
REGEXP_SUBSTR(column, regex [,start index])
and
only work on
Match the prev. pattern 2 times
{2}
LEFT
RIGHT
NVARCHAR
SELECT 'This''ll do'
Example:
Match the previous pattern between
{2,4}
columns, not literals.
two and four times
This'll do
REGEXP_SUBSTR(x,'[0‐9].[0‐9].[0‐9]')
Gotcha!
Matches any character that is (or isn’t -
Avoid confusion!
[a‐dX],
'1.9.6'
Trailing whitespace on the right is automatically
if ^ is used) either a,b,c,d or X.
[^a‐dX]
A double quote is a way to qualify an identifier:
clipped
A dash (‐) character between two other
Returns the first occurrence index of the pattern. Syntax
SELECT "public"."t"."firstname"
characters forms a range.
(SQL Server standard, see Pattern matching in the next
Example:
matches any decimal
[0‐9]
Search and Matching
column):
Measurements
digit.
To include a literal ] character, it must
PATINDEX(pattern, column)
Where
x is 'SQream DB v1.9.6' (VARCHAR)
Example:
immediately follow the opening bracket
LENGTH('SQream DB ') 9
and
y is 'DB' (VARCHAR)
[.
LENGTH('Flüßigkeit') 10
PATINDEX('%[0‐9]%',x)
To include a literal dash (‐) character,
'1'
OCTET_LENGTH('SQream DB') 9
ISPREFIXOF(needle,haystack)
it must appear first or last.
OCTET_LENGTH('Flüßigkeit') 12
Returns 1/0 (= true/false) if x is prefix of y.
Any character that does not have a
Like
defined special meaning inside a []
Example:
Used in a WHERE clause to search for a specified
pair matches only itself.
ISPREFIXOF(x,y)
pattern in a column
0
Example:
SELECT x FROM t WHERE x LIKE '%DB%'
'SQream DB v1.9.6'
'DB'
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education