Posts Tagged 'sql'

SQL Server – Enter Value from Keyboard

Here’s another of those things I forget if I don’t use SQL Server for a while.

To enter a value of <NULL> into a field in SQL Server just type:
[Ctrl] + 0

(Hold down the Ctrl key and press the zero key).

Advertisements

SQL SERVER – SUBSTRING- The Equivalent of the VBA MID Function

Following on from my previous post regarding the SQL Server CHARINDEX function here’s some information about the SUBSTRING function for those of you, like me, tend to think in VBA!

The SQL Server equivalent of the VBA MID function is SUBSTRING.

We can use it to find a part of a string.

For example:

SUBSTRING(“This is text”,3,2) would return the string “is”.

Microsoft defines the function’s syntax as follows.

Syntax

SUBSTRING ( expression , start , length )

Arguments

expression

Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start

Is an integer that specifies where the substring begins.

length

Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.

Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.

SQL SERVER – CHARINDEX – The Equivalent of the VBA InStr Function

Doing 90% of my work in ASP and VBA I often find that I forget about the CHARINDEX function within SQL Server!

In ASP I’d usually do something like:

p = instr(myString, " ")

to find the first instance of the space character within the string myString.

On SQL Server the equivalent is the CHARINDEX function.

This finds the first occurance of one string within another string.

For example:


CHARINDEX(" ","My Blog")

would return the value 3.


Microsoft’s full description of this function is as follows:

Syntax

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

Arguments

expression1

Is an expression that contains the sequence of characters to be found. expression1 is an expression of the character string data type category.

expression2

Is an expression, typically a column searched for the specified sequence. expression2 is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or is zero, the search starts at the beginning of expression2. start_location can be of type bigint.

Return Types

bigint if expression2 is of the varchar(max), nvarchar(max) or varbinary(max) data types, otherwise int.
Remarks

If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

The starting position returned is 1-based, not 0-based.


Blog Stats

  • 222,709 hits

Counter

ClustrMap