How to work with SQL Server Text Data Type (CLOBs) in Toad Data Point

I had a user tell me that it would be really powerful if Toad Data Point could search through SQL Server text columns in a table and then create a calculated field based upon the search results of that column. I thought to myself, "surely that's possible in Toad Data Point," but when I tried, I was surprised that the column with my text data doesn't even show up in Transform and Cleanse. "What's going on?" I asked myself.

For example, in the following screenshot you can see my SQL Server table representing Products a company might sell. The fourth column named is ProductDescription, which contains a description of the products in my table. You may already spot the difference between this column and others, it is defined in SQL Server as “text” while others are “nvarchar” data type:

When I send this table directly to the Transform and Cleanse, the Product Description column is missing. You see columns like ProductName, which is stored as nvarchar, but you don’t see ProductDescription stored as text:

When I look at the data tab or run a query and look at the results tab, I see the following “Excluded” message:

However, when I double click on the column, it displays this message:

Once I saw this, I had a better idea of what was going on. "Text" in SQL Server stores data as large object binaries, or LOBs. More specifically, text characters are stored in a character large object binary, or CLOB. I'm use to Oracle which stores large text in the data type LONG or CLOB data types and I'm not as experienced on SQL Server. Toad Data Point does such a great job of abstracting database concepts that I forget not all databases are the same.

It takes a bit of extra processing to display these data types, so by default it's just shown as "Excluded." Toad won’t show you the data for CLOBs unless you specifically request for it. If the ProductDescription column was instead a "ProductManual" that had pages and pages of text per product, I probably don’t need that to be displayed if I’m just prototyping some queries.

After I click "Read LOBs" the query runs again and displays the entire text this time. And now when I send the data to the Transform and Cleanse function, the ProductDescription column is displayed:

From here, it's a simple matter of using the "Group Columns" feature of Transform and Cleanse. You can find Group Columns on the toolbar or by right clicking on the column itself. My next screenshot shows the right-click menu:

For this example, if the ProductDescription field contains the string "LCD", then the product must have a screen, so I'm using this logic to create a new column that says "SCREEN" with the value of Yes or No:

And here's the result of my Transform and Cleanse:

You can set the behavior of LOBs in the options menu under Tools | Options | Database | General | Read LOBs:

I hope that little tidbit was helpful for you, it certainly was for my user. It got my head scratching for a bit there, but I’m always thrilled to provide a solution to someone!