Phillip.Putzback (5/2/2014)

---

Maybe I should simplify it. Let's say you need to report on Midwest states in many reports, ETLS, or stored procedures... Would it not be easier to have a...

You could start by posting the correct information. The values that you posted can't be stored in an int column. You have either the incorrect column or the incorrect logic....

CHARINDEX doesn't accept wildcards, that why we need to use PATINDEX.

Are you looking for something like this? Or what is your expected output?

WITH SampleData AS(

SELECT 'xxIN187 13-14 W Elem' CalendarId UNION ALL

SELECT...

Real column names or data don't matter. Real data types matter and the structure of the data as well to be sure that we can give an accurate solution.

pooyan_pdm (4/30/2014)

---

well, in some cases it could have some performance impact depending on the workload, disk subsystem,...assuming you've also set the log backup jobs on your databases, but this...

It took me a while to go through your code to figure out what it was doing.

As stated before, you just need to add a default to your parameter. But...

Grant Fritchey (4/30/2014)

---

There is a tiny impact on performance because you're using resources to backup the logs. But it's really tiny. If you're already at 99% resource usage, adding this...

rock.liu (4/30/2014)

---

can you use this one ?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4

FROM boxes a

cross apply (select top 1 b.field1 from widgets b...

The Pattern Splitter made by Chris Morris is able to help you in this situation. You can read about it in this article: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

And you'll end up with something like...

Why would you want to delete a row that has just been inserted in a table?

If you really need something like that (instead of directly executing the program) you could...

I'm not sure that I understand exactly what you want. First of all, it's nonsense to store a column with a value that will be the same for all rows.

Second,...

What about using the code 120?

CONVERT( datetime, @myDate, 120)

Something like this?

WITH SampleData(ID, Source, Rank) AS(

SELECT 123, 'Foo', 10 UNION ALL

SELECT 123, 'Bar', 20 UNION ALL

SELECT 124,...

If ID is the PK, why is it repeated? PKs should be unique.