SELECTISNULL(p.FirstName, '') + ISNULL(',' + p.MiddleName, '') + ISNULL(',' + p.LastName, '') AS Name,

SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Monday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],

SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) =...

Firday? You mean Friday?

Add an IDENTITY column to the table, and make your "unique" column as a persisted computed column.

Also see this topic

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

for more calculations on IDENTITY column.

GSquared (8/14/2008)

---

If you really want 0-99 in a semi-random sequence, it would be much easier to do this:

select number

from dbo.numbers

where number between 0 and 99

order by checksum(cast(number as varchar(2)) +...

OP never wrote "unlimited" number of random records.

He wrote he needed random records with 4 characters in length that would never [from the original set] repeat.

I was just kidding with previous post.

But here is a way to get 10,000 unique nonrepeating random numbers between 0000 and 9999

In this example, I have set to environment to...

SELECTABS(CHECKSUM(GETDATE())) % 10000

Add this sample data

union all

SELECT 205,null,'',null,''

and trry again.

SELECT'Set No ' + SetNo + ': '

+ SUBSTRING(

ISNULL(',' + NULLIF(Reason1, ''), '')

+ ISNULL(',' + NULLIF(Reason2, ''), '')

+ ISNULL(',' + NULLIF(Reason3, ''), '')

+...

Try this

SELECTHC_IH_TD_ACTUAL_TIME_FROM,

HC_IH_TD_ACTUAL_TIME_TO,

CONVERT(CHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, TimeFrom, TimeTo), '00:01'), 108) AS TimeDifference

FROM(

SELECTHC_IH_TD_ACTUAL_TIME_FROM,

CASE

WHEN HC_IH_TD_ACTUAL_TIME_FROM LIKE '[0-2][0-9]:[0-9][0-9]' THEN HC_IH_TD_ACTUAL_TIME_FROM

ESLE NULL

END AS TimeFrom,

HC_IH_TD_ACTUAL_TIME_TO,

CASE

WHEN HC_IH_TD_ACTUAL_TIME_TO LIKE '[0-2][0-9]:[0-9][0-9]' THEN HC_IH_TD_ACTUAL_TIME_TO

ESLE NULL

END AS TimeTo

FROMdbo.HC_IH_TIMESHEET_DETAIL

) AS d

Here is an updated version of fnExtractPostCodeUK function.

CREATE FUNCTION dbo.fnExtractPostCodeUK

(

@data VARCHAR(8000)

)

RETURNS VARCHAR(8)

AS

BEGIN

RETURN COALESCE(

-- AANN NAA

SUBSTRING(@Data,...

Here is an updated version of fnValidatePostCodeUK function.

Thank you Jeff for spotting the errors.

//Peso

CREATE FUNCTION dbo.fnValidatePostCodeUK

(

@PostCode VARCHAR(8)

)

RETURNS BIT

AS

BEGIN

RETURNCASE

-- AANN NAA

WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

-- AANA NAA

WHEN @PostCode LIKE...

This is a SQL Server 2000 forum, but you write you have SQL Server 2005.

SELECT Col1, Col2, Col3

FROM (

SELECT Col1, Col2, Col3, ROW_NUMBER() OVER (PARTITION BY DrawingID ORDER BY Revision...

I don't think you need to use function twice in query.

Update @TestTab

Set Postcode = coalesce(postcode, dbo.fnExtractPostCodeUK(add1))

PATINDEX approach as an inline function

CREATE FUNCTIONdbo.fnExtractPostCodeUK

(

@Data VARCHAR(8000)

)

RETURNS VARCHAR(8)

AS

BEGIN

RETURNCOALESCE(

SUBSTRING(@Data, NULLIF(PATINDEX('%[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]%', @Data), 0), 8),

SUBSTRING(@Data, NULLIF(PATINDEX('%[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY0123456789][ABCDEFGHJKSTUW0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]%', @Data), 0), 7),

SUBSTRING(@Data, NULLIF(PATINDEX('%[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]%', @Data), 0), 6),

SUBSTRING(@Data, NULLIF(PATINDEX('%GIR 0AA%', @Data), 0), 7)

)

END

Not really a regular expression but instead an inline function

CREATE FUNCTIONdbo.fnPostCodeUK

(

@PostCode VARCHAR(8)

)

RETURNS BIT

AS

BEGIN

RETURNCASE

WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY0123456789][ABCDEFGHJKSTUW0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN...