Supported ClickHouse functions

This is an ever-expanding list of enabled ClickHouse functions.

You can find their full definitions in the ClickHouse documentation. Additionally, we include a list of popular ones and their uses in the HogQL expressions and SQL insight documentation.

Arithmetic

  • plus
  • minus
  • multiply
  • divide
  • intDiv
  • intDivOrZero
  • modulo
  • moduloOrZero
  • positiveModulo
  • negate
  • abs
  • gcd
  • lcm
  • max2
  • min2
  • multiplyDecimal
  • divideDecimal

Arrays and strings in common

  • empty
  • notEmpty
  • length
  • reverse
  • in
  • notIn

Arrays

  • array
  • range
  • arrayConcat
  • arrayElement
  • has
  • hasAll
  • hasAny
  • hasSubstr
  • indexOf
  • arrayCount
  • countEqual
  • arrayEnumerate
  • arrayEnumerateUniq
  • arrayPopBack
  • arrayPopFront
  • arrayPushBack
  • arrayPushFront
  • arrayResize
  • arraySlice
  • arraySort
  • arrayReverseSort
  • arrayUniq
  • arrayJoin
  • arrayDifference
  • arrayDistinct
  • arrayEnumerateDense
  • arrayIntersect
  • arrayReverse
  • arrayFilter
  • arrayFlatten
  • arrayCompact
  • arrayZip
  • arrayAUC
  • arrayMap
  • arrayFill
  • arraySplit
  • arrayReverseFill
  • arrayReverseSplit
  • arrayExists
  • arrayAll
  • arrayFirst
  • arrayLast
  • arrayFirstIndex
  • arrayLastIndex
  • arrayMin
  • arrayMax
  • arraySum
  • arrayAvg
  • arrayCumSum
  • arrayCumSumNonNegative
  • arrayProduct

Comparison

  • equals
  • notEquals
  • less
  • greater
  • lessOrEquals
  • greaterOrEquals

Logical

  • and
  • or
  • xor
  • not

Type conversions

  • toInt
  • toFloat
  • toDecimal
  • toDate
  • toDateTime
  • toUUID
  • toString
  • toJSONString
  • parseDateTime
  • parseDateTimeBestEffort

Dates and times

  • toTimeZone
  • timeZoneOf
  • timeZoneOffset
  • toYear
  • toQuarter
  • toMonth
  • toDayOfYear
  • toDayOfMonth
  • toDayOfWeek
  • toHour
  • toMinute
  • toSecond
  • toUnixTimestamp
  • toStartOfYear
  • toStartOfISOYear
  • toStartOfQuarter
  • toStartOfMonth
  • toLastDayOfMonth
  • toMonday
  • toStartOfWeek
  • toStartOfDay
  • toStartOfHour
  • toStartOfMinute
  • toStartOfSecond
  • toStartOfFiveMinutes
  • toStartOfTenMinutes
  • toStartOfFifteenMinutes
  • toTime
  • toISOYear
  • toISOWeek
  • toWeek
  • toYearWeek
  • age
  • dateDiff
  • dateTrunc
  • dateAdd
  • dateSub
  • timeStampAdd
  • timeStampSub
  • now
  • NOW
  • nowInBlock
  • today
  • yesterday
  • timeSlot
  • toYYYYMM
  • toYYYYMMDD
  • toYYYYMMDDhhmmss
  • addYears
  • addMonths
  • addWeeks
  • addDays
  • addHours
  • addMinutes
  • addSeconds
  • addQuarters
  • subtractYears
  • subtractMonths
  • subtractWeeks
  • subtractDays
  • subtractHours
  • subtractMinutes
  • subtractSeconds
  • subtractQuarters
  • timeSlots
  • formatDateTime
  • dateName
  • monthName
  • fromUnixTimestamp
  • toModifiedJulianDay
  • fromModifiedJulianDay
  • toIntervalSecond
  • toIntervalMinute
  • toIntervalHour
  • toIntervalDay
  • toIntervalWeek
  • toIntervalMonth
  • toIntervalQuarter
  • toIntervalYear

Strings

  • lengthUTF8
  • leftPad
  • rightPad
  • leftPadUTF8
  • rightPadUTF8
  • lower
  • upper
  • lowerUTF8
  • upperUTF8
  • isValidUTF8
  • toValidUTF8
  • repeat
  • format
  • reverseUTF8
  • concat
  • substring
  • substringUTF8
  • appendTrailingCharIfAbsent
  • convertCharset
  • base58Encode
  • base58Decode
  • tryBase58Decode
  • base64Encode
  • base64Decode
  • tryBase64Decode
  • endsWith
  • startsWith
  • trim
  • trimLeft
  • trimRight
  • encodeXMLComponent
  • decodeXMLComponent
  • extractTextFromHTML
  • ascii
  • concatWithSeparator

Searching in strings

  • position
  • positionCaseInsensitive
  • positionUTF8
  • positionCaseInsensitiveUTF8
  • multiSearchAllPositions
  • multiSearchAllPositionsUTF8
  • multiSearchFirstPosition
  • multiSearchFirstIndex
  • multiSearchAny
  • match
  • multiMatchAny
  • multiMatchAnyIndex
  • multiMatchAllIndices
  • multiFuzzyMatchAny
  • multiFuzzyMatchAnyIndex
  • multiFuzzyMatchAllIndices
  • extract
  • extractAll
  • extractAllGroupsHorizontal
  • extractAllGroupsVertical
  • like
  • ilike
  • notLike
  • notILike
  • ngramDistance
  • ngramSearch
  • countSubstrings
  • countSubstringsCaseInsensitive
  • countSubstringsCaseInsensitiveUTF8
  • countMatches
  • regexpExtract

Replacing in strings

  • replace
  • replaceAll
  • replaceOne
  • replaceRegexpAll
  • replaceRegexpOne
  • regexpQuoteMeta
  • translate
  • translateUTF8

Conditional

  • if
  • multiIf

Mathematical

  • e
  • pi
  • exp
  • log
  • ln
  • exp2
  • log2
  • exp10
  • log10
  • sqrt
  • cbrt
  • erf
  • erfc
  • lgamma
  • tgamma
  • sin
  • cos
  • tan
  • asin
  • acos
  • atan
  • pow
  • power
  • intExp2
  • intExp10
  • cosh
  • acosh
  • sinh
  • asinh
  • atanh
  • atan2
  • hypot
  • log1p
  • sign
  • degrees
  • radians
  • factorial
  • width_bucket

Rounding

  • floor
  • ceil
  • trunc
  • round
  • roundBankers
  • roundToExp2
  • roundDuration
  • roundAge
  • roundDown

Maps

  • map
  • mapFromArrays
  • mapAdd
  • mapSubtract
  • mapPopulateSeries
  • mapContains
  • mapKeys
  • mapValues
  • mapContainsKeyLike
  • mapExtractKeyLike
  • mapApply
  • mapFilter
  • mapUpdate

Splitting strings

  • splitByChar
  • splitByString
  • splitByRegexp
  • splitByWhitespace
  • splitByNonAlpha
  • arrayStringConcat
  • alphaTokens
  • extractAllGroups
  • ngrams
  • tokens

Bit

  • bitAnd
  • bitOr
  • bitXor
  • bitNot
  • bitShiftLeft
  • bitShiftRight
  • bitRotateLeft
  • bitRotateRight
  • bitSlice
  • bitTest
  • bitTestAll
  • bitTestAny
  • bitCount
  • bitHammingDistance

Bitmap

  • bitmapBuild
  • bitmapToArray
  • bitmapSubsetInRange
  • bitmapSubsetLimit
  • subBitmap
  • bitmapContains
  • bitmapHasAny
  • bitmapHasAll
  • bitmapCardinality
  • bitmapMin
  • bitmapMax
  • bitmapTransform
  • bitmapAnd
  • bitmapOr
  • bitmapXor
  • bitmapAndnot
  • bitmapAndCardinality
  • bitmapOrCardinality
  • bitmapXorCardinality
  • bitmapAndnotCardinality

URLs

  • protocol
  • domain
  • domainWithoutWWW
  • topLevelDomain
  • firstSignificantSubdomain
  • cutToFirstSignificantSubdomain
  • cutToFirstSignificantSubdomainWithWWW
  • port
  • path
  • pathFull
  • queryString
  • fragment
  • queryStringAndFragment
  • extractURLParameter
  • extractURLParameters
  • extractURLParameterNames
  • URLHierarchy
  • URLPathHierarchy
  • encodeURLComponent
  • decodeURLComponent
  • encodeURLFormComponent
  • decodeURLFormComponent
  • netloc
  • cutWWW
  • cutQueryString
  • cutFragment
  • cutQueryStringAndFragment
  • cutURLParameter

JSON

  • isValidJSON
  • JSONHas
  • JSONLength
  • JSONArrayLength
  • JSONType
  • JSONExtractUInt
  • JSONExtractInt
  • JSONExtractFloat
  • JSONExtractBool
  • JSONExtractString
  • JSONExtractKey
  • JSONExtractKeys
  • JSONExtractRaw
  • JSONExtractArrayRaw
  • JSONExtractKeysAndValuesRaw

Geo

  • greatCircleDistance
  • geoDistance
  • greatCircleAngle
  • pointInEllipses
  • pointInPolygon

Nullable

  • isNull
  • isNotNull
  • coalesce
  • ifNull
  • nullIf
  • assumeNotNull
  • toNullable

Tuples

  • tuple
  • tupleElement
  • untuple
  • tupleHammingDistance
  • tupleToNameValuePairs
  • tuplePlus
  • tupleMinus
  • tupleMultiply
  • tupleDivide
  • tupleNegate
  • tupleMultiplyByNumber
  • tupleDivideByNumber
  • dotProduct

Time window

  • tumble
  • hop
  • tumbleStart
  • tumbleEnd
  • hopStart
  • hopEnd

Distance window

  • L1Norm
  • L2Norm
  • LinfNorm
  • LpNorm
  • L1Distance
  • L2Distance
  • LinfDistance
  • LpDistance
  • L1Normalize
  • L2Normalize
  • LinfNormalize
  • LpNormalize
  • cosineDistance

Other

  • isFinite
  • isInfinite
  • ifNotFinite
  • isNaN
  • bar
  • transform
  • formatReadableDecimalSize
  • formatReadableSize
  • formatReadableQuantity
  • formatReadableTimeDelta

Community questions

Was this page useful?

Questions about this page? or post a community question.