Mssqlserver-charindex the magical, find the magical

CharIndex

1:charindex syntax: CharIndex (Expression1,expression2[,start_location])

2: Parameter expression1 An expression that contains the sequence of characters to find. Expression1 is an expression of a string data category. Expression2 An expression, typically a column that is searched for a specified sequence. Expression2 belongs to the string data category. Start_location the character position at which to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or 0, the search begins at the beginning of the Espression2. Start_location can be of type bigint.

3: Return type if the data type of expression2 is varchar (max), nvarchar (max) or varbinnary (max), then bigint, otherwise Int.

4: Note If expression1 is not found within Expression2, CHARINDEX returns 0. Charindex performs a comparison operation based on the collation entered. To compare with a specified collation, you can use collate to apply an explicit collation to the input value. The starting position of the return starts at 1, not starting at 0.

5: Example

Select CHARINDEX (' A ', ' BADF ', 1): 2

Select CHARINDEX (' A ', ' BADF ', 0): 2

Select CHARINDEX (' A ', ' BADF ', 2): 2

Select CHARINDEX (' A ', ' BADF ', 3): 0

Select CHARINDEX (' G ', ' BADF ', 3): 0

Second, substring

1. Grammar

SUBSTRING (expression,start,length);

2. Parameters

Expression

is a string, a binary string, a text, an image, a column, or an expression that contains a column. Do not use an expression that contains an aggregate function.

Start

An integer that specifies the starting position of the string. Start can be a bigint type.

Length

An integer that specifies the number of characters or bytes of expression to return. If length is negative, an error is returned. Length can be a bigint type.

3. Notes

The offset (start and length) that uses the ntext, char, or varchar data type must be specified as a number of characters. You must specify the offset of the data type using text, image, binary, or varbinary, in bytes.

4. Example:

Select substring (' GXS ', -1,3): G

Select substring (' GXS ', -1,2): NULL

Select substring (' GXS ', 0,1): NULL

Select substring (' GXS '): G

Select substring (' GXS ', up): GX

Substring and charindex used together
Select substring (' A,b,c,d ', 0,charindex (', ', ' A,b,c,d ', 0))

Integer except Integer, if the last is an integer, the data is correct, find the wrong data (find the data with decimal point)

SELECTPMX. Djbh,sp. SPDM,PD. Byzd7,pmx.sl,sp. Cust_spbzgg,CONVERT(FLOAT, PMX.SL)/CONVERT(FLOAT, SP. CUST_SPBZGG),CHARINDEX('.',CONVERT(NVARCHAR( -),CONVERT(FLOAT, PMX.SL)/CONVERT(FLOATSp. CUST_SPBZGG)),0) frompfjrdmx PMX Left JOINPFJRD PD onPMX. Djbh=PD.DJBH Left JOINDbo. Shangpin SP onPMX. SPDM=SP. SPDMWHEREPd. BYZD7= '004'  andSp. Cust_spbzgg!=1--and CHARINDEX ('. ', convert (NVARCHAR), convert (FLOAT,PMX.SL)/convert (float,sp. CUST_SPBZGG)), 0) >0 and CHARINDEX('.',CONVERT(NVARCHAR( -),CONVERT(FLOAT, PMX.SL)/CONVERT(FLOATSp. CUST_SPBZGG)),0)!='0'

Mssqlserver-charindex the magical, find the magical