Feeds:
Posts
Comments

Archive for the ‘String’ Category

String or binary data would be truncated (Error number 8152) is a very common error. It usually happens when we try to insert any data in string (varchar,nvarchar,char,nchar) data type column which is more than size of the column. So you need to check the data size with respect to the column width and identify which column is creating problem and fix it. It is very simple if you are dealing with less columns in a table. But it becomes nightmare if you are dealing with inert into query with huge number of columns and you need to check one by one column. I received this query from one of my Blog readers Mr Ram Kumar asking if there is a shortcut to resolve this issue and give the column name along with the data creating problems. I started searching for the solution but could not get proper one. So I started developing this solution.
Before proceeding with the solution, I would like to create a sample to demonstrate the problem.

SAMPLE :

--This script is compatible with SQL Server 2005 and above.
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [NAME] VARCHAR(10),
)
GO
INSERT INTO tbl_sample VALUES (1,'Bob Jack Creasey')
GO
INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,'Frank Richard Wedge')
GO
--OUTPUT

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

SOLTUION :
Given below is the stored procedure that can find the exact column name and its data which is exceeding the limit of column width.

--DROP PROCEDURE usp_String_or_binary_data_truncated
--GO
CREATE PROCEDURE usp_String_or_binary_data_truncated
@String VARCHAR(MAX)
AS

DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,'''','')
             ,'[',''),']',''),CHAR(13) + CHAR(10),'')
SET @Xml = CAST(('<a>'+REPLACE(@String,'(','</a><a>')
           +'</a>') AS XML)

SELECT @TCount=COUNT(*)
FROM @Xml.nodes('A') AS FN(A)

;WITH CTE AS
     (SELECT
     (CASE
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))>0)
     THEN 1
     WHEN CHARINDEX('VALUES',A.value('.', 'varchar(max)'))>0
     THEN 2
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=2  THEN 2
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=3  THEN 3
     END) AS[Batch Number],
     REPLACE(REPLACE(A.value('.', 'varchar(max)')
     ,'INSERT INTO',''),'VALUES ','') AS [Column]
     FROM @Xml.nodes('A') AS FN(A))

, [CTE2] AS
(
    SELECT
	[Batch Number],
	CAST('' + REPLACE([Column], ',' , '')
    + '' AS XML)
    AS [Column name And Data]
    FROM  [CTE]
)
,[CTE3] AS
(
    SELECT [Batch Number],
	ROW_NUMBER() OVER(PARTITION BY [Batch Number]
    ORDER BY [Batch Number] DESC) AS [Row Number],
    Split.a.value('.', 'VARCHAR(MAX)') AS [Column name And Data]
FROM [CTE2]
CROSS APPLY [Column name And Data].nodes('/M')Split(A))

SELECT
 ISNULL(B.[Column name And Data],C.name) AS [Column Name]
,A.[Column name And Data] AS [Column Data]
,C.max_length As [Column Length]
,DATALENGTH(A.[Column name And Data])
AS [Column Data Length]

FROM [CTE3] A
LEFT JOIN [CTE3] B
ON A.[Batch Number]=2 AND B.[Batch Number]=3
AND A.[Row Number] =B.[Row Number]
LEFT JOIN sys.columns C
ON C.object_id =(
	SELECT object_ID(LTRIM(RTRIM([Column name And Data])))
	FROM [CTE3] WHERE [Batch Number]=1
)
AND (C.name = B.[Column name And Data]
OR  (C.column_id =A.[Row Number]
And A.[Batch Number]<>1))
WHERE a.[Batch Number] <>1
AND DATALENGTH(A.[Column name And Data]) >C.max_length
AND C.system_type_id IN (167,175,231,239)
AND C.max_length>0

GO

EXAMPLE :
Now, you simply need to replace all single quotes of your insert into query to double quotes and pass it into the stored procedure.
Given below is the sample.

EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample VALUES (1,''Bob Jack Creasey'')'
GO
EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,''Frank Richard Wedge'')'
GO
--OUTPUT

string or binary data truncated.1.1

As you can see above, it returned only the column name(s) whose data sizes exceed the limit of the column width.
Do let me know if you come across situation like that and resolve it in a different ways.

Advertisements

Read Full Post »

I received this query from one of my consultants, when he was busy migrating legacy data for one of our customers, enquiring how to check if some of the employee job titles have carriage return and line feed? Whether there is any solution I can provide to check in any string that it has carriage return and line feed or not. This is the first time, I came across this query so I started searching the solution, but could not get a proper solution. So I started developing the solution. Given below is the script.

SOLUTION :

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
--GO
CREATE FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
(
      @String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_BOOLEAN INT

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_BOOLEAN =COUNT(*)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1)) 
IN (13,10)    

RETURN (CASE WHEN @RETURN_BOOLEAN >0 THEN 'TRUE' ELSE 'FALSE' END)
END
GO

EXAMPLE :
In this example, I manually inserted a carriage return and line feed in employee table to test the above solution using given below script.

USE AdventureWorks2012
GO
UPDATE [HumanResources].[Employee] SET [JobTitle] ='Research
and
Development Manager' WHERE BusinessEntityID =6

Lets browse the employee table using above solution and check. Given below is the script.

USE AdventureWorks2012
GO
SELECT BusinessEntityID,OrganizationLevel,JobTitle
,dbo.[UDF_Check_existance_of_carriage_return_line_feed] ([JobTitle])
AS [Boolean]
FROM HumanResources.Employee
--OUTPUT

carriage return and linefeed.1.1

Read Full Post »

I came across this query ‘how to add & view multiline texts in any varchar field’ when our team was migrating a customer data from legacy system, and one of our team members tried to update the comments field in a column of a table.

Before processing with the solution, I would like to create a sample table to demonstrate the solution.

SAMPLE :

USE Tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [Col_ID] INT,
 [Col_varchar] varchar(50)
)
GO

SOLUTION :

In this solution, we can copy and paste the same text covered with single quote and insert into varchar column. Given below is the script.

USE Tempdb
GO
INSERT INTO tbl_sample VALUES (1,'This is a sample
comment on raresql.com to demonstrate
multiline text')

Lets browse the table and view the record that is it updated as a multiline or not. Given below is the script.

USE Tempdb
GO
SELECT * FROM tbl_sample
GO
-OUTPUT

multiline text.1.1

Opssssss, it is not updated as multiline in the column ;). Unfortunately if you view it in a grid it will not show as a multiline text. So to resolve it, you need to change from results to grid to results to text (Crtl+T).

multiline text.1.2

Read Full Post »

Today, I was searching for some scripts from my scripts library and I came across one of my handy scripts that help me to trim all columns in a table.  You do not need to trim column by column. It will trim all the string columns at once. Given below is the script. I usually use it to clean / massage the data.

SOLUTION :

USE AdventureWorks2012
GO
--DROP PROCEDURE usp_trim_all_string_columns
--GO
CREATE PROCEDURE usp_trim_all_string_columns
@schema_Table_name VARCHAR(MAX)
AS
DECLARE @SQL AS VARCHAR(MAX)

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])
+ ' = LTRIM(RTRIM(' + QUOTENAME([name]) + '))' FROM
sys.columns WHERE object_id=object_id(@schema_Table_name)
AND system_type_id IN(35,99,167,175,231,29)
FOR XML PATH('')),1,1,'')
PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL

EXEC(@SQL)
GO

EXAMPLE :

USE AdventureWorks2012
GO
EXEC usp_trim_all_string_columns '[HumanResources].[Employee]'
GO

Read Full Post »

‘How to remove the last character in a string’ is a general problem that we usually face while developing a dynamic SQL string or sometimes due to legacy data etc.

Given below are multiple solutions to remove the last character from a string.

SOLUTION 1 : Using LEFT string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,LEFT(@String,DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

SOLUTION 2 : Using STUFF string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,STUFF(@String,DATALENGTH(@String), 1, '')
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

SOLUTION 3 : Using SUBSTRING string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,SUBSTRING(@String,1, DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

Read Full Post »

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
 [ID] INT,
 [NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

remove extra spaces.1.2

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
   @String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space VARCHAR(MAX)
DECLARE @delimiter VARCHAR(5)

SET @delimiter=' '
SET @Xml = CAST(('<a>'+REPLACE(@String,@delimiter,'</a><a>')+'</a>')
AS XML)

;WITH CTE AS (SELECT
 A.value('.', 'VARCHAR(MAX)') AS [Column]
 FROM @Xml.nodes('A') AS FN(A))

 SELECT @Removed_Extra_Space=REPLACE(
 Stuff((
 SELECT ';' + A.[Column]
 FROM CTE A
 WHERE ISNULL(A.[Column],'') <>''
 FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

Read Full Post »

Few days earlier, I was working on a legacy application data and found that some characters were merged with the numbers in a column of a table and the issue was, I had to get only the numbers from this column.
Given below are the samples which were available in the column.
129 mph
550 kmp

Let me create a sample to demonstrate the problem and its solution.

USE tempdb
GO
--Create a table
DECLARE @tbl_sample TABLE
(
ID INT,
Varchar_col VARCHAR(10)
)
--Insert sample records in the table
INSERT INTO @tbl_sample VALUES (1,'12.9 mph')
INSERT INTO @tbl_sample VALUES (2,NULL)
INSERT INTO @tbl_sample VALUES (3,'45')
INSERT INTO @tbl_sample VALUES (4,'90 mph')

--Browse the table
SELECT * FROM @tbl_sample

remove trailing characters.1.1

SOLUTION :
Given below is the solution using string manipulation function.

SELECT
 ID
,Varchar_col
,LEFT(Varchar_col
,DATALENGTH(Varchar_col)-
(PATINDEX('%[0-9]%',REVERSE(Varchar_col))-1)) AS [Number]
FROM @tbl_sample

remove trailing characters.1.2

Let me know if you came across this situation and resolved it with the different solution.

Read Full Post »