:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Apr 26 / 2011
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

T-SQL wild card characters for use with like operator

SQL Server provides four wild card characters to use with like operator. These are % , _ , [], [^]. These wild card characters provide very powerful capabilities to operate on string data. At morning i was required to operate on a column with strings containing digits. There were few values where alphabets were also in the value. I was required to separate the values which also contain  alphabets along with digits.
This can be done by using above mentioned wild card characters in T-SQL. Following is full example

 -- Create Table in Specified database  
 IF EXISTS (SELECT * FROM sys.objects  
 WHERE name = N'StringsTest' AND [type] = 'U')  
 DROP TABLE StringsTest  
 CREATE TABLE StringsTest (StringValue varchar(230))  
 GO  
 -- Populate the table  
 INSERT INTO StringsTest  
 SELECT '120-35-AQS' UNION ALL  
 SELECT '180-35-ASP' UNION ALL  
 SELECT '15335'    UNION ALL  
 SELECT '158-895'  UNION ALL  
 SELECT '158895'    UNION ALL  
 SELECT 'ASDBFC'    UNION ALL    
 SELECT 'ACVFRHB'  UNION ALL    
 SELECT 'ABC-158-35'    
 GO  
 -- To get values where alphabets are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[A-Z]%'  
 -- To get values where digits are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[^A-Z]%'  
 -- To get values where Q,R or S follows -A  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%-_[Q-S]%'  

In the same way you can build many more regular expressions as required in a specific scenario.

  • Oct 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using parameter for rows with TOP in SELECT statement

T-SQL allows us to retrieve a specific number of rows by using TOP operator in SELECT statement. Following statement would retrieve top 10 rows containing name and number from PRODUCT table.

SELECT TOP 10 Name, ProductNumber  
 FROM [AdventureWorks].[Production].[Product]  
 GO  

Of course you may filter the rows by using WHERE clause and other ways as used in normal select statement. It is fine till here, until you are required to change the provided number (10 in above statement) with some dynamic parameter with TOP clause. Trying following code would generate error

 
DECLARE @rows int  
 SET @rows = 13  
 SELECT TOP @rows Name, ProductNumber  
 FROM Production.Product  
 GO  

Solution:

Solution to this problem is simple. Just enclose the parameter in SELECT statement with in a bracket. So our working code would be some thing like

 DECLARE @rows int  
 SET @rows = 13  
 SELECT TOP(@rows) Name, ProductNumber  
 FROM Production.Product   
 GO  

You may use this syntax in functions, procedures or scripts when required to use Using parameter for rows with TOP in SELECT statement. I have test this syntax on SQL Server 2005 and SQL Server 2008 instances and it worked well. May be it would not work on SQL Server 2000 (i read but not tested).

  • Apr 30 / 2010
  • 0
dbDigger, String Manipulations, T-SQL Interview Questions, T-SQL Tips and Tricks

T-SQL String concatenation

If you are going to google the terms like ‘T-SQL concatenate’ to view the syntax of assumed concatenate or concate built-in function, then there is no need to do it. T-SQL allows you to concatenate two or more strings by just using + sign among strings.
To experience, use following code line in SSMS

print 'string1 '+'concatenated to '+'string2'

It would provide following result

SSMS result for concat sign

  • Feb 01 / 2010
  • 0
Data Modeling and Database Design, dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Insert value in identity column when identity column is only column in table

I came across a question that how to insert value in identity column when identity column is only column in the table. And to set identity insert on is not allowed. It is interesting scenario. So answer is to use DEFAULT VALUES. Consider following code for demo that how to insert value in identity column when identity column is only column in table.

USE AdventureWorks  
GO  
CREATE TABLE #TestInsert(onlyColum TINYINT IDENTITY (1,1))  
GO  

INSERT INTO #TestInsert DEFAULT VALUES 
GO  

SELECT * FROM #TestInsert  
GO 

And you would get inserted value from this temporary table.

Consult us to explore the Databases. Contact us