Quote SQL Server identifiers

Quotes SQL Server identifiers. Handles embedded quotes.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51:

open System
open System.Text

[<CompiledName("QuoteIdentifier")>]
let quoteIdentifier id quotePrefix quoteSuffix = 
    let isEmpty = String.IsNullOrEmpty
    let notEmpty = not << isEmpty
    let prefix, suffix = quotePrefix, quoteSuffix
    let equal strA indexA strB = (String.CompareOrdinal(strA, indexA, strB, 0, strB.Length) = 0)
    let getNext start =
        let builder = new StringBuilder()
        let append (s:string) = builder.Append(s) |> ignore
        let quoted = 
            if notEmpty prefix then
                append prefix
                equal id start prefix
            else false
        let index = if quoted then start + prefix.Length else start
        let rec loop i n =
            if i = id.Length then (i, n)
            else
                if notEmpty suffix && equal id i suffix then
                    if (i + suffix.Length) = id.Length then (i, id.Length)
                    elif id.[i + suffix.Length] = '.' then (i, i + suffix.Length + 1)
                    else
                        append suffix
                        append suffix
                        loop (if (equal id (i + suffix.Length) suffix) then i + 2 else i + 1) n
                else
                    if not quoted && id.[i] = '.' then (i, i + 1)
                    else
                        append (id.[i].ToString())
                        loop (i + 1) (i + 1)
        let _, next = loop index index
        if notEmpty suffix then append suffix
        (builder.ToString(), next)
    let split() = 
        0 
        |> Seq.unfold (function 
            | i when i = id.Length -> None 
            | i -> Some (getNext i)) 
        |> Seq.toArray
    if isEmpty id then id
    else String.Join(".", split())

//Usage:
let quote id = quoteIdentifier id "[" "]"
quote "dbo.MyTable" //Output: "[dbo].[MyTable]"
quote "dbo.My[Table" //Output: "[dbo].[My[Table]"
quote "dbo.My]Table" //Output: "[dbo].[My]]Table]"
quote "dbo.[MyTable]" //Output: "[dbo].[MyTable]"

namespace System

namespace System.Text

Multiple items
type CompiledNameAttribute =
  inherit Attribute
  new : compiledName:string -> CompiledNameAttribute
  member CompiledName : string

Full name: Microsoft.FSharp.Core.CompiledNameAttribute

--------------------
new : compiledName:string -> CompiledNameAttribute

val quoteIdentifier : id:string -> quotePrefix:string -> quoteSuffix:string -> string

Full name: Script.quoteIdentifier

val id : string

val quotePrefix : string

val quoteSuffix : string

val isEmpty : (string -> bool)

Multiple items
type String =
  new : value:char -> string + 7 overloads
  member Chars : int -> char
  member Clone : unit -> obj
  member CompareTo : value:obj -> int + 1 overload
  member Contains : value:string -> bool
  member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
  member EndsWith : value:string -> bool + 2 overloads
  member Equals : obj:obj -> bool + 2 overloads
  member GetEnumerator : unit -> CharEnumerator
  member GetHashCode : unit -> int
  ...

Full name: System.String

--------------------
String(value: nativeptr<char>) : unit
String(value: nativeptr<sbyte>) : unit
String(value: char []) : unit
String(c: char, count: int) : unit
String(value: nativeptr<char>, startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int) : unit
String(value: char [], startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int, enc: Encoding) : unit

String.IsNullOrEmpty(value: string) : bool

val notEmpty : (string -> bool)

val not : value:bool -> bool

Full name: Microsoft.FSharp.Core.Operators.not

val prefix : string

val suffix : string

val equal : (string -> int -> string -> bool)

val strA : string

val indexA : int

val strB : string

String.CompareOrdinal(strA: string, strB: string) : int
String.CompareOrdinal(strA: string, indexA: int, strB: string, indexB: int, length: int) : int

property String.Length: int

val getNext : (int -> string * int)

val start : int

val builder : StringBuilder

Multiple items
type StringBuilder =
  new : unit -> StringBuilder + 5 overloads
  member Append : value:string -> StringBuilder + 18 overloads
  member AppendFormat : format:string * arg0:obj -> StringBuilder + 4 overloads
  member AppendLine : unit -> StringBuilder + 1 overload
  member Capacity : int with get, set
  member Chars : int -> char with get, set
  member Clear : unit -> StringBuilder
  member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
  member EnsureCapacity : capacity:int -> int
  member Equals : sb:StringBuilder -> bool
  ...

Full name: System.Text.StringBuilder

--------------------
StringBuilder() : unit
StringBuilder(capacity: int) : unit
StringBuilder(value: string) : unit
StringBuilder(value: string, capacity: int) : unit
StringBuilder(capacity: int, maxCapacity: int) : unit
StringBuilder(value: string, startIndex: int, length: int, capacity: int) : unit

val append : (string -> unit)

val s : string

Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string

StringBuilder.Append(value: char []) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: obj) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: uint64) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: uint32) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: uint16) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: decimal) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: float) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: float32) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: int64) : StringBuilder
   (+0 other overloads)
StringBuilder.Append(value: int) : StringBuilder
   (+0 other overloads)

val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore

val quoted : bool

val index : int

val loop : (int -> int -> int * int)

val i : int

val n : int

val next : int

StringBuilder.ToString() : string
StringBuilder.ToString(startIndex: int, length: int) : string

val split : (unit -> string [])

module Seq

from Microsoft.FSharp.Collections

val unfold : generator:('State -> ('T * 'State) option) -> state:'State -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.unfold

union case Option.None: Option<'T>

union case Option.Some: Value: 'T -> Option<'T>

val toArray : source:seq<'T> -> 'T []

Full name: Microsoft.FSharp.Collections.Seq.toArray

String.Join(separator: string, values: Collections.Generic.IEnumerable<string>) : string
String.Join<'T>(separator: string, values: Collections.Generic.IEnumerable<'T>) : string
String.Join(separator: string, [<ParamArray>] values: obj []) : string
String.Join(separator: string, [<ParamArray>] value: string []) : string
String.Join(separator: string, value: string [], startIndex: int, count: int) : string

val quote : id:string -> string

Full name: Script.quote

Raw view Test code New version

More information

Link:	http://fssnip.net/26
Posted:	13 years ago
Author:	Daniel Robinson
Tags:	sql server , t-sql , identifiers