SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Trim Leading and Trailing quotes before inserting to database – Sql Server

on February 9, 2012

Importing from a excel to SSIS is a tedious task. Some time we have to convert from excel to tab seprated text file and then import.

While converting from excel to tab separated file “quotes” get appended to the text.

Following is a Sql function to remove leading and trailing quotes from text and then insert to db.

</pre>
CREATE FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT, @End Int, @Length INT
SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'""', '"')
SELECT @Start = CHARINDEX('"', @Str), @End = CHARINDEX('"', REVERSE(@Str)),
@Length = LEN(@Str)
IF @Start =1
SET @Start = @Start + 1 -- new string must start from second character
ElSE
SET @Start = 1 --as there is not starting quote. we will not trim it
IF @End != 1
SET @End = 0 -- as there is not ending quote. we will not trim it

SELECT @Str = SUBSTRING(@Str,@Start, @Length - @Start - @End + 1)

RETURN @str

END
GO
SELECT dbo.TrimQuotes ( '"abc"') , dbo.TrimQuotes ( ' abc" ') , dbo.TrimQuotes ( '"a"b"c') , dbo.TrimQuotes ( 'a"b"c')
, dbo.TrimQuotes ( '"abc'), dbo.TrimQuotes ( '"a""bc'), dbo.TrimQuotes ( '"""b""c""')
<pre>
Advertisements

One response to “Trim Leading and Trailing quotes before inserting to database – Sql Server

  1. –Next version: to trim spaces and double quotes added inside text while migrating
    ALTER FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    DECLARE @Start INT, @End Int, @Length INT
    SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'””‘, ‘”‘)
    SELECT @Start = CHARINDEX(‘”‘, @Str), @End = CHARINDEX(‘”‘, REVERSE(@Str)),
    @Length = LEN(@Str)
    IF @Start =1
    SET @Start = @Start + 1 –new string must start from second character
    ElSE
    SET @Start = 1 –as there is not starting quote. we will not trim it
    IF @End != 1
    SET @End = 0 –as there is not ending quote. we will not trim it

    SELECT @Str = SUBSTRING(@Str,@Start, @Length – @Start – @End + 1)

    RETURN @str

    END

    –SELECT dbo.TrimQuotes ( ‘ “abc” ‘) , dbo.TrimQuotes ( ‘ abc” ‘) , dbo.TrimQuotes ( ‘”a”b”c’) , dbo.TrimQuotes ( ‘a”b”c’)
    –, dbo.TrimQuotes ( ‘”abc’), dbo.TrimQuotes ( ‘”a””bc’), dbo.TrimQuotes ( ‘”””b””c””‘)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: