SQL SERVER – Remove Duplicate Characters From a String

Follow up of my previous article of Remove Duplicate Chars From String here is another great article written by Madhivanan where similar solution is suggested with alternate method of Number table approach. Check out Remove duplicate characters from a string

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)

Just a day ago I received phone call from my friend in Bangalore. He asked me

What do I think of storing images in database and what kind of datatype he should use?

I have very strong opinion about this issue.

I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.

Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – UDF – Remove Duplicate Chars From String

Few days ago, I received following wonderful UDF from one of this blog reader. This UDF is written for specific purpose of removing duplicate chars string from one large string. Virendra Chauhan, author of this UDF is working as DBA in Lutheran Health Network.

(@datalen_tocheck INT,@string VARCHAR(255))
@str VARCHAR(255)
@start INT
@result VARCHAR(255)
SET @end=@datalen_tocheck
SET @count=@datalen_tocheck
SET @str = @string
WHILE (@count <=255)
(@result IS NULL)
SET @str=REPLACE(@str,SUBSTRING(@str,@start,@end),'')
SET @count=@count+@datalen_tocheck
GO>, 1)


SELECT dbo.Remove_duplicate_instr(<CHARacter length OF a

duplicate SUBSTRING >,<string contain duplicate>)
To keep char set in a string unique and remove duplicate 3 char long string run this UDF as inline function.


SELECT dbo.Remove_duplicate_instr(3,f123456789123456456

Reference : Pinal Dave (http://blog.SQLAuthority.com), Virendra Chauhan (DBA)

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

Following function return all the available collation of SQL Server 2005. My previous article about the SQL SERVER – 2005 Collation Explanation and Translation.

FROM sys.fn_HelpCollations()

Result Set: (only few of 1011 records)
Name Description
Latin1_General_BIN Latin1-General, binary sort
Latin1_General_BIN2 Latin1-General, binary code point comparison sort
Latin1_General_CI_AI Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AI_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AI_KS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AI_KS_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AS_KS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AS_KS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AI Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AI_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AI_KS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AI_KS_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AS_KS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AS_KS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.

Fix/WorkAround/Solution: Use either VARCHAR(8000) or VARCHAR(MAX) . VARCHAR(MAX) of SQL Server 2005 is replacement of TEXT of SQL Server 2000.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Repeate String N Times Using String Function REPLICATE

I came across this SQL String Function few days ago while searching for Database Replication. This is T-SQL Function and it repeats the string/character expression N number of times specified in the function.

SELECT REPLICATE( ' http://www.SQLAuthority.com ' , 9 )

This repeats the string http://www.SQLAuthority.com to 9 times in result window. I think it is fun utility to generate repeated text if ever required.

Result Set:
http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com

(1 row(s) affected)

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – 2005 Row Overflow Data Explanation

In SQL Server 2000 and SQL Server 2005 a table can have a maximum of 8060 bytes per row. One of my fellow DBA said that he believed that SQL Server 2000 had that restriction but SQL Server 2005 does not have that restriction and it can have a row of 2GB. I totally agreed with him but after we discussed this problem in depth, we realized that there are more into it than only 8060 bytes limit.

It is still true for SQL Server 2005 that a table can have maximum of 8060 bytes per row however the restriction has exclusions of few data types. CLR User Defined data-types as well as varchar, nvarchar, varbinary, sql_variants are not limited to bytes per row limitation. However, those data types have limitation that they can not be more than 8000 bytes individually. In SQL Server 2005 one table row can contain more than one varchar(8000) fields. One more thing, the exclusions have exclusions also the limit of each individual column max width of 8000 bytes does not apply to varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns. Though, sum of all other other kind of data type should be less than 8060 bytes. That means one table can not have three columns with char(4000) or 100K bit field columns.

In summary in SQL Server 2005

  • Table row can have more than 8060 bytes. (2GB Max)
  • varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns can have max 8000 bytes.
  • varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns have no restrictions.
  • All the other data type columns (other than mentioned in above three points) width addition must be still under 8060 byte row limit.
  • Index can only be created which falls with-in 8060 byte row limit.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL, SQL Stuff

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

One of the developer at my company asked is it possible to parse HTML and retrieve only TEXT from it without using regular expression. He wanted to remove everything between < and > and keep only Text. I found question very interesting and quickly wrote UDF which does not use regular expression.

Following UDF takes input as HTML and returns TEXT only. If there is any single quotes in HTML they should be replaced with two single quotes (not double quote) before it is passed as input to function.

@Start INT
@End INT
@Length INT
@Start = CHARINDEX('<',@HTMLText)
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
@HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @Length = (@End - @Start) + 1

Test above function like this :

SELECT dbo.udf_StripHTML('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

Result Set:

UDF at SQLAuthority.com SQLAuthority.com

If you want to see this example in action click on Image. It will open large image.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

One of the Jr. DBA at in my Team Member asked me question the other day when he was replacing TEXT field with VARCHAR(MAX) : How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowing 12500 characters.

Traditionally thinking we only create the field as long as we need. SQL Server 2005 does support VARCHAR(MAX) but does not support VARCHAR(12500). If we try to create database field with VARCHAR(12500) it gives following error.

Server: Msg 131, Level 15, State 3, Line 1
The size (12500) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).

The solution we came up was to put constraint on the VARCHAR(MAX). SQL SERVER 2005 supports VARCHAR(MAX) and Constraint.

CREATE TABLE [dbo].[MyTestTable] ( [VarChar12500] VARCHAR(MAX) )
ALTER TABLE [dbo].[MyTestTable]
ADD CONSTRAINT [MaxLengthConstraint]
CHECK (DATALENGTH([VarChar12500]) <= 12500)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

Yesterday, in Friday Afternoon team meeting. I was asked question by one of application developer

“I am asked in new coding standards to use VARHCAR(MAX) instead of TEXT. Is VARCHAR(MAX) big enough to store TEXT field?”

Well, I realize that I was not clear enough in my coding standard. It is extremely important for coding standards to be clear and have a enough explanation that developer have no doubt about them. I updated coding standards after the meeting. The answer is

“Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).”

There are more reasons to use VARHCAR(MAX) though this was verbal answer to technical question in our general meeting where the focus was “Web Application Architecture and SQL Server”.

Reference : Pinal Dave (http://blog.SQLAuthority.com)