SQL Tips and Tricks

Author: Nehaa Vishwakarma | Posted: 12. February 2011 18:22 5

Hello Everyone!!! Welcome to my Blog. I believe that knowledge should be shared with everyone and so I want to share all that I have learned in past 5 years of my professional experience. Currently I am working as a Senior Software Architect and Developer utilizing .NET technologies, SQL Server 2008, Dataware housing using Business Intelligence Tools like Analysis Services, Integration Services, Reporting Services, Web Services and XML.

Here is the first post of this BlogSpot which I thought could be helpful in certain scenarios that we get while working with data.

Scenario: If we have a table where we have following:

EmployeeIDName
11111111 John123
22222222 Dave456

 

And we need to separate numbers from Name and get the following output

EmployeeIDName
11111111 John
22222222 Dave

then following query can be useful

select EmployeeID,
REPLACE([Name],substring([Name],patindex(‘%[0-9]%’,[Name]),len([Name])),”) from tableNme where [Name] like ‘%[0-9]%’

Tags: , , ,

SQL Server

E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (5) | Post RSSRSS comment feed

Comments (5) -

harto Indonesia, on 2/20/2011 2:01:42 AM Said:

harto

hi,
I would like know about user-defined types in sql server 2008.. Actually i want to use its utility but so i havent been able to understand how.

so can u post something on USER-DEFINED TYPE.

Thanks in advance!!!!!

Nehaa United States, on 2/21/2011 5:11:42 PM Said:

Nehaa

Hello Harto, I will soon be posting an article on leveraging UDTs in real world application. Please keep checking

ron United States, on 3/31/2011 3:41:03 AM Said:

ron

i wish if anyone could help!!
i am trying to execute this code below in sql server but i am getting error

error-"Column  in table  is of a type that is invalid for use as a key column in an index."

create table friend
(
username nvarchar(max) not null,
friend_username nvarchar(max) not null,
primary key(username,friend_username)
)

i want primary key on combination of two fields, but not able to do so as error is commig.

Nehaa United States, on 4/1/2011 1:52:21 PM Said:

Nehaa

Hi Ron, You are trying to specify Primary Key on column where length of data is max, since Primary key creates an index automatically, an index need a specified length and try to keep it small. In your case username nvarchar(max) and friend_username nvarchar(max) will not work...

ron United States, on 4/2/2011 1:49:22 AM Said:

ron

Thankyou Nehaa!!!! I appreciate your help!!!. I will have to relook at my database design.!!!!

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading