SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'

Create A Comma Delimited List From a Column

Create A Comma Delimited List From a Column

Create Sample Data for Both Solutions

-- Suppress data loading messages SET NOCOUNT ON   -- Create Sample Data  CREATE TABLE TeamInfo    (  MemberID    int IDENTITY,       TeamID      int,        FirstName   varchar(50)    )   -- Load Sample Data INSERT INTO TeamInfo VALUES ( 1, 'Jim' ) INSERT INTO TeamInfo VALUES ( 1, 'Mary' ) INSERT INTO TeamInfo VALUES ( 1, 'Bob' ) INSERT INTO TeamInfo VALUES ( 2, 'Sue' ) INSERT INTO TeamInfo VALUES ( 2, 'Ralph' ) INSERT INTO TeamInfo VALUES ( 2, 'Ellen' ) INSERT INTO TeamInfo VALUES ( 3, 'Bill' ) INSERT INTO TeamInfo VALUES ( 3, 'Linda' )

create function -- SQL 2000, Retrieve desired data

-- With SQL 2000, we will create a User Defined Function to do the concatenation. -- While this solution can also be used with SQL Server 2005/SQL Server 2008,  -- the previous suggestion is more efficient.   CREATE FUNCTION dbo.fnMakeTeamList    (  @TeamID int  )    RETURNS varchar(1000) AS    BEGIN       DECLARE @TempTeam table          (  Firstname varchar(20)  )       DECLARE @TeamList varchar(1000)       SET @TeamList = ''       INSERT INTO @TempTeam          SELECT FirstName          FROM TeamInfo          WHERE TeamID = @TeamID       IF @@ROWCOUNT > 0          UPDATE @TempTeam             SET @TeamList = ( @TeamList + FirstName + ', ' )       RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))    END   -- Usage SELECT     TeamID,     MemberList = dbo.fnMakeTeamList( TeamId )  FROM TeamInfo  GROUP BY TeamID   -- Results TeamID     MemberList 1 Jim, Mary, Bob 2 Sue, Ralph, Ellen 3 Bill, LindaS

QL Server 2005 / SQL Server 2008 Solution

--Retrieve desired data SELECT    t1.TeamID,    MemberList = substring((SELECT ( ', ' + FirstName )                            FROM TeamInfo t2                            WHERE t1.TeamID = t2.TeamID                            ORDER BY                                TeamID,                               FirstName                            FOR XML PATH( '' )                           ), 3, 1000 )FROM TeamInfo t1 GROUP BY TeamID   -- Results TeamID     MemberList 1 Bob, Jim, Mary 2 Ellen, Ralph, Sue 3 Bill, Linda

ISNULL() and COALESCE()

ISNULL() and COALESCE()

In dealing with NULL values in SQL Server, the developer is often faced with the requirement to evaluate the content of a field, and when said field in null, return another value (or field). There are two functions in SQL Server which support this requirement: ISNULL() and COALESCE.()

ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1) --Returns 1 SELECT ISNULL(NULL, NULL) --Returns NULL 

COALESCE()

COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1) --Returns 1   SELECT COALESCE(NULL, 3, NULL, 1) --Returns 3 

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50) SET @Field2 = 'Some Long String'   SELECT ISNULL(@Field1, @Field2) --Returns 'Som' SELECT COALESCE(@Field1, @Field2) --Returns 'Some Long String' 

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it's arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren't compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime. For example:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

Performance

For most purposes, ISNULL and COALESCE perform in an almost identical fashion. It is generally accepted that ISNULL is slightly quicker than COALESCE, but not sufficiently to outweigh it's inherent limitations. As with any performance related issue, if performance is a significant concern, write it both ways, and test!

Difference between NVARCHAR and VARCHAR in SQL Server

Difference between NVARCHAR and VARCHAR in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions areVARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

Here's an example of how to mix and match the use of the two types. Let's say we have a community website where people log in with a username, but can also set a public "friendly" name to be more easily identified by other users. The login name can be a VARCHAR, which means it must be 8-bit ASCII (and it can be constrained further to conventional alphanumerics with a little more work, typically on the front end). The friendly name can be an NVARCHAR to allow Unicode entities. This way you're allowing support for Unicode, but only in the place where it matters most — both for the users, and where the extra storage space is going to be put to the best possible use.

VARCHAR and NVARCHAR in SQL Server 2005

One fairly major change to both VARCHAR and NVARCHAR in SQL Server 2005 is the creation of the VARCHAR(MAX) and NVARCHAR(MAX) data types. If you create a VARCHAR(MAX) column, it can hold up to 2^31 bytes of data, or 2,147,483,648 characters; NVARCHAR(MAX) can hold 2^30 bytes, or 1,073,741,823 characters.

These new data types are essentially replacements for the Large Object or LOB data types such as TEXT and NTEXT, which have a lot of restrictions. They can't be passed as variables in a stored procedure, for instance. The(MAX) types don't have those restrictions; they just work like very large string types. Consequently, if you're in the process of re-engineering an existing data design for SQL Server 2005, it might make sense to migrate some (although not all!) TEXT / NTEXT fields to VARCHAR(MAX) / NVARCHAR(MAX) types when appropriate.

The big difference between VARCHAR and NVARCHAR is a matter of need. If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.

sql2000 delete rows having duplicate (identical) records

Suppose, we have identical records like :-

name age

***********

RAM 30

RAM 30

we have to keep one entry each for ram:30  and ram:40 and delete duplicate entries:-

set rowcount 1 

(this will remove top 1 row)

select * from temp where name = 'ram'

delete temp where name = 'ram'

set rowcount 0