Hi

I think this covers what you want to do. I suspect the performance will be poor.

WITH groupit AS (

-- Group up the Depts on data

SELECT CID, CDate, Dept,

ROW_NUMBER() OVER...

Andreas P. Williams (9/20/2013)

---

Hi, mickyT!

Your method only works if the number of rows is a multiple of 5. Try and remove the ('J',1) and your bottom 20 per cent has...

Hi you could also use NTILE.

As an example

WITH sampledata AS (

SELECT *, NTILE(5) OVER (ORDER BY Numbers DESC) N

FROM (VALUES

('A',134)

,('B',122)

,('C',88)

,('D',82)

,('E',33)

,('F',22)

,('G',18)

,('H',4)

,('I',1)

,('J',1)

) SD(Book,Numbers)

)

SELECT Book, Numbers,

CASE WHEN N = 1 THEN...

dwain.c (9/18/2013)

---

mickyT (9/18/2013)

---

dwain.c (9/18/2013)

---

How about using a string pattern splitter?

WITH SampleData (MyStr) AS (

SELECT 'Ørstedsvej 7B' UNION ALL

SELECT 'Volvo 25D' UNION ALL

SELECT '104ND Nokia'

)

SELECT MyStr

...

dwain.c (9/18/2013)

---

How about using a string pattern splitter?

WITH SampleData (MyStr) AS (

SELECT 'Ørstedsvej 7B' UNION ALL

SELECT 'Volvo 25D' UNION ALL

SELECT '104ND Nokia'

)

SELECT MyStr

...

If you only have a single replacement like in your example then this should do the trick

SELECT STUFF(val,PATINDEX('%[0-9][a-z]%',val) + 1,0,' ')

FROM (VALUES

('Ørstedsvej 7B')

,('Volvo 25D')

,('104ND Nokia')

) SD(VAL)

If you have more than...

Are those geometry columns in your query? If so you will not be able to include them in group by clause.

As Sean said some DDL and sample...

Hi Nick

You could try the following. It makes use of PARSENAME and Jeff Modens DelimitedSplit8K function that can be found here[/url]

declare @line varchar(8000) = 'HEADER*Company.A*Company.B*5001*8/22/2009^CATEGORY*Parts*PRT^LINE*PartA*11^LINE*PartF*5^LINE*PartG*37^CATEGORY*Supplies*SUP^LINE*SupplyK*1^LINE*SupplyY*88^LINE*SupplyG*72^CATEGORY*Materials*MTR^LINE*MaterialQ*202^';

select

-- Header components

replace(

parsename(

replace(replace(replace(

substring(@line,1,CHARINDEX('^',@line)-1) --...

Ditto on Dwain's comment, not a good idea.

You may cause yourself more issues down the line following this particular plan. For example are there any current queries...

I always try to put the table I am updating as first in the FROM clause and use the alias as the UPDATE target.

UPDATE DC

SET DocType = SUBSTRING(STG.CurrDocNumber,1,1)

FROM DIM.DocControlProfile AS...

Koen Verbeeck (9/11/2013)

---

ChrisM@Work (9/11/2013)

---

Hany Helmy (9/11/2013)

---

I am really amazed of the 16 people (as of now) who got it right! :w00t:

Hope one of them will post a reply explaining how...

Either or will work, the difference being that STIntersects will also work on 2008 as well where STContains will not.

There could also be an arguement for using STWithin

declare @container geography...

Hi

You can do a query like the following

DECLARE @pointgeo GEOGRAPHY = Geography::STGeomFromText('POINT (-122.56153600 45.603118166)', 4326)

SELECT c.Name

FROM Country c

WHERE c.Shape.STIntersects(@pointgeo) = 1

Just make sure that you SRIDs match.

Here's a link...

Hi

Given that your points are likely to be fairly squarely placed an average of them will probably give you pretty good an consistent results

Probably the best method would be...

Another option is

SELECT PATINDEX('%[^ ]%',@s) - 1

Not sure if it'll be any quicker, but you could add in tabs into the pattern as well