SQL Function Archives - Page 48 of 49 - SQL Authority with Pinal Dave

SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String

April 11, 2007

Following SQL User Defined Function will extract/parse numbers from the string. CREATE FUNCTION ExtractInteger(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers =…

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

April 8, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Server, SQL Server Security, SQL Utility

We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking…

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

March 29, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Joins, SQL Scripts

This question is asked many times to me. What is difference between DISTINCT and GROUP BY? A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If…

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

March 25, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Index, SQL Scripts

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is…

SQL SERVER – SQL Commandments – Suggestions, Tips, Tricks

March 20, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, Database, Software Development, SQL Backup and Restore, SQL Constraint and Keys, SQL DateTime, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Joins, SQL Server DBCC, SQL Server Security, SQL Stored Procedure

Few days ago, while searching for something on web site, I came across a very good article of 25 SQL Commandments. I really enjoyed reading it. It was for Oracle, I re-wrote it for SQL Server. First 18 points are taken from original article and last 2 I added to…

SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running

March 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

To determine which version of SQL Server 2000/2005 is running, connect to SQL Server 2000/2005 by using Query Analyzer, and then run the following code: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The results are: The product version (for example, 8.00.534). The product level (for example, “RTM” or “SP2”). The edition…

SQL SERVER – Creating Comma Separate List From Table

February 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Stored Procedure, SQL String

Update : (5/5/2007) I have updated the script to support SQL SERVER 2005. Visit :SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP Reference : Pinal Dave (https://blog.sqlauthority.com)