Some table defs, sample data and actual required results would be helpful

But I consulted my Oiuja board  and.....

SELECT [Category],[ProductName],[CompanyName]

FROM

(SELECT 1 AS [Category],p.[ProductName],p.[CompanyID]

FROM [Product] p...

Search the script section on this site, for example

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1789

I use this

set nocount on

/*

Create the temp tables to hold the results of DBCC

commands until the information is entered into

DatabaseSpaceUsage

*/

CREATE TABLE...

LEFT([name],LEN([name])-CHARINDEX(' ',REVERSE([name]),PATINDEX('%[0-9]%',REVERSE([name])))+1) AS [name],

RIGHT([name],CHARINDEX(' ',REVERSE([name]),PATINDEX('%[0-9]%',REVERSE([name])))-1) AS [name]

may give you better results overall but I think you need to profile the data to see if there is any definate...

You will get the error if the column does not contain a number preceeded by a space

if so try this

LEFT([name],PATINDEX('% [0-9]%',[name]+' 1')-1) AS [name],

SUBSTRING([name],PATINDEX('% [0-9]%',[name]+' 1')+1,255) AS [version]

Note the spaces...

...highlight that space in red ...

didn't think of that

Ooops! Sorry about that

[CustomerID] LIKE '[A-Z][a-z][a-z][0-9]'

AND ASCII(SUBSTRING([CustomerID],1,1)) = ASCII(UPPER(SUBSTRING([CustomerID],1,1)))

AND ASCII(SUBSTRING([CustomerID],2,1)) = ASCII(LOWER(SUBSTRING([CustomerID],2,1)))

AND ASCII(SUBSTRING([CustomerID],3,1)) = ASCII(LOWER(SUBSTRING([CustomerID],3,1)))

or use

RTRIM(LEFT([column],PATINDEX('% [0-9]%',[column])-1)) AS [name]

SUBSTRING([column],PATINDEX('% [0-9]%',[column]),255) AS [version]

if you want to include a space before the number in case the name portion contains numbers

RTRIM(LEFT([column],PATINDEX('%[0-9]%',[column])-1)) AS [name]

SUBSTRING([column],PATINDEX('%[0-9]%',[column]),255) AS [version]

Is it possible for the string not to have a number?

A FOREIGN KEY can be null if taken in isolation

A column can be NULL providing ALL the CONSTRAINTS applied to it allow it

So

A column that has a PRIMARY KEY...

Setting a column to be an IDENTITY column will not make it a primary key. The primary key of a table is for you to decide. Making an IDENTITY column...

Yes, use an INSTEAD OF trigger

CREATE TRIGGER [triggername] on

INSTEAD OF INSERT

AS

BEGIN

    INSERT INTO ([column1],[column2])

    SELECT a.[column1],a.[column2]

    FROM [inserted] a

    LEFT OUTER...

1.

ALTER TABLE ADD CONSTRAINT [constraintname] UNIQUE NONCLUSTERED ([column1],[column2])

Look up 'constraints, UNIQUE' in Books Online (BOL)

2.

INSERT INTO ([column1],[column2])

SELECT a.[column1],a.[column2]

FROM [anothertable] a

LEFT OUTER JOIN b

ON...

Good catch Vladan

Obvious when someone shows you how