TSQL: fnPadLeft: fills up a string with padChar from left

— Description: fills up a string  with @len  @padChar+@var from left

— Example: SELECT dbo.[fnPadLeft](‘1’, ‘0’, 3) returns ‘001’

— =============================================

CREATE Function[dbo].[fnLBCustomPadLeft]

(@varvarchar(max),

@padCharchar(1)=‘ ‘,

@lenint)

returnsvarchar(max)

as
begin

return replicate(@PadChar,@lenLen(@var))+@var

end

 

TSQL: fnCharCount: counting char in a string

CREATE  FUNCTION[dbo].[fnCharCount](@stringvarchar(8000),@characterchar(1))

RETURNS SMALLINT

AS

BEGIN

DECLARE@stringtruncvarchar(8000);

SET@stringtrunc=REPLACE(@string,@character,);— remove the specified character

RETURN (LEN(@string)LEN(@stringtrunc));— return the difference in length, this is the char count

END

CLR: strip discouraged characters from a string for xml vb.net

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
”’ <summary>
”’ removes discouraged characters
”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154
”’ </summary>
”’ <param name=”Content”>string for xml</param>
”’ <returns>clean SqlString</returns>
”’ <remarks>
”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.
”’ see http://www.w3.org documentation:
”’ </remarks>
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character
Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then
Return String.Empty
End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _
OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _
OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _
OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then
result.Append(cC)
End If
Next
Return New SqlString(result.ToString())
End Function
End Class

Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions    ”’ <summary>    ”’ removes discouraged characters     ”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154    ”’ </summary>    ”’ <param name=”Content”>string for xml</param>    ”’ <returns>clean SqlString</returns>    ”’ <remarks>    ”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.    ”’ see http://www.w3.org documentation:    ”’ http://www.w3.org/TR/2008/REC-xml-20081126/#charsets    ”’ http://www.w3.org/TR/xml11/#charsets    ”’ </remarks>    <Microsoft.SqlServer.Server.SqlFunction()> _    Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character        Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then            Return String.Empty        End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _             OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _             OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _             OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then                result.Append(cC)            End If
Next
Return New SqlString(result.ToString())
End FunctionEnd Class

CLR: arithmetic overflow while insert into a table containing a datetime column

problem: you get an arithmetic overflow error while you want in your CLR stored procedure to insert into a table containing a datetime column
reason: datetime-format on sql serveris set up differently than you use it
workaround:  you can use SET DATEFORMAT before you call you CLR stored procedures.

example:

USE Database
GO

DECLARE    @iReturnvalue int
–set datetime format you want
SET DATEFORMAT ymd;
GO

— call CLR stored procedure
EXEC   [dbo].[sp] @iMandant = 1, @iReturnvalue = @iReturnvalue OUTPUT

TSQL: user defined function split

CREATE FUNCTION [dbo].[udfSplit](@text nvarchar(max), @delimiter char(1) = ‘ ‘)

RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max) )

AS

BEGIN

DECLARE @index int

SET @index = -1

WHILE (LEN(@text) > 0)

BEGIN SET @index = CHARINDEX(@delimiter , @text)

IF (@index = 0) AND (LEN(@text) > 0)

BEGIN

INSERT INTO @Strings VALUES (@text) BREAK

END

IF (@index > 1)

BEGIN

INSERT INTO @Strings VALUES (LEFT(@text, @index – 1))

SET @text = RIGHT(@text, (LEN(@text) – @index))

END

ELSE

SET @text = RIGHT(@text, (LEN(@text) – @index))

END

RETURN

END

parameters:

@text: text to split

@delimiter: character

returns:

table with columns position und value

SELECT * FROM [OLReweAbf].[dbo].[udfSplit] (‘test’,’t’)

position value
1 es

example:

Update LBFulfillmentKNORueckmeldungAuftrag

SET PaketGew = (select sum(cast([value] as bigint)) from [dbo].[udfSplit](PaketGew,’:’))

WHERE VersandartID LIKE ‘LKW%’ AND PaketGew LIKE ‘%:%’ AND TrackingNr is NULL