I don't agree with storing values as separated lists.

But, to answer your question, I have made the assumption that TB_CongVan is the parent table, and cap2 is the child table.

SELECT

...

Thanks MadAdmin

I could use something like this

SELECT mt.*, p.partition_number

FROM MyTableName AS...

You can always import it in batches

1 - Insert a new column before "A". Add a UniqueKey to this column. It could be as simple as an incrementing...

It's very difficult to attempt an answer without sample data and expected output.

That said, perhaps this will do the trick

SELECT src.*,

TotalEntered = SUM(Entered) OVER (PARTITION BY USERNAME),

...

OK. So I have tweaked Scott's math and massaged it into a CROSS APPLY against the table to search for any partial PartNumber, regardless of length.

The performance...

DesNorton (4/6/2016)

---

ScottPletcher (4/6/2016)

---

DesNorton (4/6/2016)

Hi Scott

This is certainly fast, with similar I/O to the other solutions, but very low CPU numbers. However, I am unable to wrap my head around...

ScottPletcher (4/6/2016)

---

DesNorton (4/6/2016)

Hi Scott

This is certainly fast, with similar I/O to the other solutions, but very low CPU numbers. However, I am unable to wrap my head around the...

ScottPletcher (4/5/2016)

---

Another possibility is to leave the main table alone, and create a separate table to hold the partial partids to match on. Use a trigger on the main...

Alan.B (4/5/2016)

---

Now let's compare your query when the optimizer uses a clustered index to when a UNIQUE nonclustered index is available (if you run this with "include actual execution plan"...

Scott and Alan

I have just walked into a #$@%storm that needs my full attention. I will take a closer look at your suggestions as soon as I can.

Sergiy (4/5/2016)

---

DesNorton (4/5/2016)

---

I have inherited a DB, and need to make some changes for a search proc.

This app and DB are still in dev, and have not yet been published...

Alan.B (4/5/2016)

---

Interesting dilemma and great work with the sample data/ddl!

Quick question what are the Partnumber column always going to be 6 characters long or less?

Also (and I expect...

Alan.B (4/5/2016)

---

Interesting dilemma and great work with the sample data/ddl!

Quick question what are the Partnumber column always going to be 6 characters long or less?

Also (and I expect...

Alan.B (4/1/2016)

---

Regarding the first question - that's an excellent observation, good catch. When I was writing the function I hadn't decided how long the search pattern would be. You are...

Alan.B (3/30/2016)

---

This is the kind of thing that you can do with a Translate function (which T-SQL does not have). I generally don't like scalar UDFs but this guy is...