SQL
SQL Server Strings
Strings:
| String function | example | Answer | Description |
|---|---|---|---|
| Ascii | Select Ascii(‘A’) | 65 | Ascii of capital A |
| Ascii | Select Ascii(‘a’) | 97 | Ascii of small a |
| char | Select Char(65) | A | Alphabet of Ascii 65 |
| char | Select Char(97) | a | Alphabet of Ascii 97 |
| charindex | Select CHARINDEX(‘i’,’String’) | 4 | Position of the search string |
| charindex | Select CHARINDEX(‘i’,’Stringi’,5) | 7 | Position of the search string starting from 5th position |
| concat | Select Concat (‘SQL’,’String’) | SQLString | Combines strings |
| datalength | Select Datalength(‘ABC ‘) | 5 | Trailing spaces are included |
| datalength | Select Datalength (‘ ABC’) | 5 | Leading spaces are included |
| datalength | Select Datalength (‘AB C’) | 5 | Spaces in the string are included |
| format | Select Format(1234567890,’###-###-####’) | 123-456-7890 | Formats the value |
| left | Select Left(‘String’,3) | Str | Extracts the left 3 characters |
| len | Select Len(‘ABC ‘) | 3 | Trailing spaces are ignored |
| len | Select Len(‘ ABC’) | 5 | Leading spaces are included |
| len | Select Len(‘AB C’) | 5 | Spaces in the string are included |
| ltrim | SELECT LTRIM(‘ String’) | String | Trims spaces on the left |
| patindex | SELECT PATINDEX(‘%[0-9]%’,’SQL6 String’) | 4 | Returns the position of the first occurrence of pattern in a string. Wild cards can be used |
| quotename | SELECT QUOTENAME(‘ABCDEF’) | [ABCDEF] | Adds square brackets |
| replace | SELECT REPLACE(‘SQL replace example’,’ ‘,’*’) | SQL*replace*example | Replaces all occurrences of a string with another |
| replicate | SELECT REPLICATE(‘P’,3) | PPP | Replicates the string specified number of times |
| reverse | SELECT REVERSE(‘ABC’) | CBA | Reverses a string |
| rtrim | SELECT RTRIM(‘String ‘) | String | Trims spaces on the right |
| string_split | SELECT VALUE FROM STRING_SPLIT(‘THIS,IS,SQL,String’,’,’) | THIS IS SQL String | Splits values separated by delimiter to columns. In this case the delimiter is comma |
| stuff | SELECT STUFF(‘SQLString’,4,3,’*’) | SQL*ing | Replace part of a string with another. In this case starting from 4th character, 3 characters are replaced by * |
| substring | SELECT Substring(‘Sql String’,5,3) | Str | Extract part of string. In this case part of string is extracted starting from 5th position, 3 characters |
| trim | SELECT TRIM(‘ String ‘) | String | Trim spaces both on left and right side of string |