SQL Code Snippets - Replace Arbitrary Spaces

Often there is a need to replace arbitrary spaces in a string in SQL.  Often this is solved using functions, ctes, or other long methods of string replacement.  Oddly, the fastest that I have found is from Try, Catc... Finally it uses a simply set of replacement statements to reduce the number of spaces in a string.

The basic issue is if you have an set of strings:

"This is      a string    with spaces"
"   This is a            string     with spaces"

how do you make them equal to each other by removing arbitrary spaces so that it says

"This is a string with spaces"

I have made some small changes to the code in the mentioned blog, and the code I use is the following:

SELECT REPLACE(REPLACE(REPLACE('This is      a string    with spaces' ,' ',CHAR(7) + CHAR(8) ),CHAR(8) + char(7),''),CHAR(7) + char(8),' ')

The reason I made the change was so that I would be using characters that should never accidentally show up in regular text data.  Or are at least much less prevalent than the less than and greater than character.

This is also another great resource on this problem that explores the many other methods, and even provides the ability to do a speed test on the various methods.

http://improvingsoftware.com/2011/03/21/replacing-multiple-spaces-in-a-string-with-a-single-space-in-sql/

SQL Code Snippets - Assign Random Number within Range to a set of Records

So lets say we want to assign a random number from 1 to 4 to every record coming out of a system.  How do we do this?

Intuitively if we want 100 random numbers we would use

select top 100 rand() from sysobjects

And then manipulate it some way, like   

select top 100  cast(((4 + 1) - 1) * rand() + 1 as int)  from sysobjects

Unfortunately that does not work, so this is a better way.

ABS(CHECKSUM(NewId()) % <range>) + <offset from 0> --- Note the + 1 makes it so that it cannot be 0.

The Absolute value is because checksums can be positive or negative (the range returned is + or - the <range> value and falls on the number line from -<range> + 1 to <range> -1 So if we set range to 4 we get the numbers  -3, -2, -1,0,1,2,3 after applying absolute value we get 0,1,2,3 after applying the offset we get 1,2,3,4

An example you can run to see this for yourself:

select

              top 100 id,name,ABS(CHECKSUM(NewId()) % 4) + 1 as [MyRandomNumber]

from sysobjects

id	name	MyRandomNumber
3	sysrscols	3
5	sysrowsets	4
7	sysallocunits	4
8	sysfiles1	1
17	syspriorities	1
19	sysfgfrag	1
23	sysphfg	1
24	sysprufiles	4
25	sysftinds	2
27	sysowners	2

SQL Code Snippets - Randomly Ordered Row Number

Sometimes you just want to generate a row number for records that is randomly ordered.

RecordOrder, RowOrder
1,2
2,4
3,1
4,3

Or something like that.  You want nothing fancy, nothing to base the order on, no groupings.  Just a simple, straight forward method for order

     ,row_number() over(partition by 1 order by newid() asc) row_num

Example from my ExampleETL database:

id	name	row_num
4.47E+08	sysssislog	7
6.71E+08	SSIS Configurations	2
6.87E+08	FILELIST	1
7.35E+08	PERSON	8
7.99E+08	COMPANY	3
8.47E+08	COMPANYPERSONRELATIONSHIP	10
8.79E+08	COMPANYPERSON	4
9.59E+08	ExampleDatabaseToDatabaseTransfer	9
9.75E+08	PRODUCT	6
1.02E+09	PRODUCTCOMPANYPRICE	5

Note that the row_num assigned was within the range requested, and assigned randomly, it should provide different results when run repeatedly.

SQL Code Snippets - Line by Line as Bulk

This code does the following.

It emulates the behavior of a line by line (cursor based) importer using bulk commands when inserting into a transaction table.

If your line by line importer would have created multiple transactions, each cascading on top of the previous transaction
this can emulate that behavior, using whatever business rules that you desire.

Here, for example, the business rule is that if the previous record had a value, and the new record is a null value
then the previous value takes precedence, but if the new record has a value, it takes precedence, regardless of the
previous value.

Theoretically, with a clever case statement in the recursive step, you could easily generate a situation where
if the previous value was of higher value than a "new" value, you could "keep" the previous value.  I leave this
to be implemented by the developer.

This code creates 2 sets of data.  It creates a previous state profile transaction, IE, a table with multiple transaction,
each updated/imported using a line by line or this same bulk importer, with two items already in it.
ordernum represents the order, in the real version this would be transaction id, which would be unique, and correctly
orderd via the inserts into a source  or some other ordering table.

The code then uses a CTE to get the latest of the transaction records, and uses that as the basis of the first
"merge" transaction that is created (the select before the UNION in the recursive CTE step).

Note the use of the left join, this makes it so that the myid=2 record is still merged into the data set correctly, since
no imports for that ID had yet been done.

The recursive step then unions each subsequent set of values, in the proper order, and produces the result, including
all intermediate results as if each row had been merged individually using a line by line importer.

IE

if the start set were:
1,2,null
1,3,4
1,4,null


Each intermediate step would be as such

1,2,null
1,3,4
1,4,4

I had a need to write this because of a situation where if an unique id came in multiple times, I wanted to be
able to capture each individual previous step to keep consistent.  This code is now simplified for your use if you also have such a merge need.

IF you were going to do this behavior similar to a an audit table, IE you have a table with a single entry,
and then an audit table with multiple entries, you can insert all of the values into a temp table.

Then "insert" the value of the current table as well as the all but the latest values into the audit table, and then
update the table with the latest value.

IE in the 1,2,null example.

If the single value in the table it is in starteed as

1,2,null

YOu would insert 1,2,null into the audit table
1,3,4 into the audit table
and update the main table with 1,4,4

Hope this helps!

*The output below selects from two tables, the ordered original table, and the line by line table so that you can
verify the results.

Original input data, with ordernum as the input order, this is an import from the first "file."

myid	ordernum	comment	commenttwo	commentthree
1	1	test	NULL	test1
1	2	concat	NULL	concat4
1	3	concat2	concat4	concat4
1	4	concat3	concat6	concat3
1	5	concat2	NULL	concat3
2	6	testx	NULL	NULL
2	7	concaxt	NULL	concaxt
2	8	concaxt2	notnull	notnull
2	9	concaxt3	NULL	concaxt3

Data for the second "file" imported:

myid	ordernum	comment	commenttwo	commentthree
1	1	FromImport1	NULL	NULL
1	2	FromImport2Update	AlsoFromImport2	AlsoFromImport2

Output data, the records are now showing, transactionally, as they would if inserted/updated using a line by line processor.

myid	comment	commenttwo	commentthree	row_num
1	test	AlsoFromImport2	test1	1
1	concat	AlsoFromImport2	concat4	2
1	concat2	concat4	concat4	3
1	concat3	concat6	concat3	4
1	concat2	concat6	concat3	5
2	testx	NULL	NULL	1
2	concaxt	NULL	concaxt	2
2	concaxt2	notnull	notnull	3
2	concaxt3	notnull	concaxt3	4

IF object_id('tempdb..#transaction') IS NOT NULL

BEGIN

   DROP TABLE #transaction

END

IF object_id('tempdb..#tmp') IS NOT NULL

BEGIN

   DROP TABLE #tmp

END

CREATE TABLE #transaction

(

myid int

, ordernum int

, comment varchar(255)

, commenttwo varchar(255)

, commentthree varchar(255)

)

CREATE TABLE #tmp

(

myid int

, ordernum int

, comment varchar(255)

, commenttwo varchar(255)

, commentthree varchar(255)

)

INSERT INTO #transaction

(

myid

,ordernum

, comment

, commenttwo

, commentthree

)

select

1,1, 'FromImport1',null,null

UNION

select

1,2, 'FromImport2Update','AlsoFromImport2','AlsoFromImport2'

INSERT INTO #tmp

(

myid

,ordernum

, comment

, commenttwo

, commentthree

)

select

1,1, 'test',null,'test1'

UNION select

1,2, 'concat', null,'concat4'

UNION select

1,3, 'concat2','concat4','concat4'

UNION select

1,4, 'concat3','concat6', 'concat3'

UNION select

1,5, 'concat2',NULL, 'concat3'

UNION

select

2,6, 'testx',null,null

UNION select

2,7, 'concaxt',null, 'concaxt'

UNION select

2,8, 'concaxt2','notnull','notnull'

UNION select

2,9, 'concaxt3',null,'concaxt3'

SELECT * FROM #tmp

;WITH cte_getlatestprofileytransaction AS

(

      SELECT

            *

      FROM

      (

            SELECT

            *

            ,row_number() over(partition by pint.myid order by ordernum desc) row_num

            FROM #transaction pint

      ) example

      WHERE row_num = 1

),

rownumsfororder  as

(

/*  Sort the  records in the order you want to concatonate them for there id  in this case we use row num!*/

select

myid,

    comment ,

      commenttwo,

      commentthree

,   ROW_NUMBER() OVER (

        PARTITION BY myid

        ORDER BY ordernum asc

    ) row_num

FROM #tmp

)

, Concatenations  AS

(

--Recursively concactonate the records in the order defined above, this makes lots of records and makes this super slow!  I bet

-- there is a trick somewhere that can do this faster!

  SELECT

    rnfo.myid,

    isnull(rnfo.comment,cglpi.comment) comment,

      isnull(rnfo.commenttwo,cglpi.commenttwo) commenttwo,

      isnull(rnfo.commentthree,cglpi.commentthree) commentthree,

    rnfo.row_num

  FROM

    rownumsfororder rnfo

      LEFT JOIN cte_getlatestprofileytransaction cglpi

      ON rnfo.myid = cglpi.myid

  WHERE

    rnfo.row_num = 1

  UNION ALL

  SELECT

    c.myid,

    isnull(l.comment , c.comment) comment,

    isnull(l.commenttwo , c.commenttwo) commenttwo,

    isnull(l.commentthree , c.commentthree) commentthree,

    l.row_num

  FROM   Concatenations c -- this is a recursion!

    INNER JOIN rownumsfororder l ON

        l.myid = c.myid

        AND l.row_num = c.row_num + 1

)

select

*

FROM Concatenations

ORDER BY myid,row_num