Quick Character Script for SQL Server CHAR()

Quick post - I had to find and replace crazy characters in one of our CHAR, NCHAR, and NVARCHAR data types.

Since there are only 256 characters, you'll only get that many results

DECLARE @i INT = 0
DECLARE @c TABLE ( char_number INT, char_desc NVARCHAR(1))

WHILE @i < 310
BEGIN

INSERT INTO @c(char_number, char_desc)
SELECT @i AS eye_d, CHAR(@i) AS char_desc

SET @i = (ISNULL(@i,310) + 1)
IF CHAR(@i) IS NULL SET @i = 310

END
SELECT * FROM @c

Comments

Dynamic Numeric List SQL

A few times I've needed to create a numbered list from a SQL script where the underlying table didn't have numeric data types. Sometimes something like this is from an internal customer, other times it is needed for development purposes. It's a fairly easy script that could be easily implemented so you don't have to make a temp table or variable table just to have a numbered list. The keyword and syntax is as follows: ROW_NUMBER () OVER ( PARTITION BY <column_name> ORDER BY <column_name> ) That’s it! As a quick example, here it is on the AdventureWorks2012 database. The query: SELECT p . FirstName , p . LastName , e . JobTitle , ROW_NUMBER () OVER ( PARTITION BY p . PersonType ORDER BY p . LastName ) AS place FROM person . person p INNER JOIN HumanResources . Employee e ON e . BusinessEntityID = p . BusinessEntityI...

Get Column Names of reports in SQL Server's ReportServer

So here’s the deal. We have a lot of reports at my place of employment. Some reports are exact copies of each other with the exception of a few columns. When users request a new report to be built, we can generally direct them to one of these reports. For our (searching) purposes this can be a bit laborious because we have to open the report to see the SQL so that we can see the columns that are being pulled to see if the columns/data points are what the users are looking for. I figured an easier way of doing this was to search the SQL Server reporting database (from now on referred to as its proper name ReportServer) for column names. I’ll walk through the code here. To start off, I created some variables because I’ll need to do some additional processing in a variable table: DECLARE @t TABLE ( eye_d INT IDENTITY ( 1 , 1 ), ItemID UNIQUEIDENTIFIER , Name VARCHAR ( 500 ), Type INT , typedescription VARCHAR ( 100 ), content IMAG...

Digital Increment

Search This Blog