Return all records where the field has the same value plus additional text

Question

The table contains unique records for a specific field (FILENAME). Although the entries are unique, they are actually just duplicates that contain only text. How can you return and group similar or similar records and update empty fields?

The table below is typical for entries. Each entry has a file name, but this is not a key field. There is one database entry with metadata that I would like to populate for document metadata that can only be identified by the first n characters.

A variable is the original file name that always changes the length of a character. The constant is that the prefix is ​​always the same.

FILENAME / DWGNO / PROJECT 52349 / 52349 / Ford 52349-1.dwg / / 52349-2.DWG / / 52349-3.dwg / / 52351 / 52351 / Toyota 52351_C01_REV- / / 52351_C01_REV2- / / 123 / 123 / Nissan 123_rev1 / / 123_rev2 / / 123_rev3 / / 

The table should look like this.

 FILENAME / DWGNO / PROJECT 52349 / 52349 / Ford 52349-1.dwg / 52349 / Ford 52349-2.DWG / 52349 / Ford 52349-3.dwg / 52349 / Ford 52351 / 52351 / Toyota 52351_C01_REV- / 52351 / Toyota 52351_C01_REV2-/ 52351 / Toyota 123 / 123 / Nissan 123_rev1 / 123 / Nissan 123_rev2 / 123 / Nissan 123_rev3 / 123 / Nissan 

At first I tried to join the table and check the length, but "LEFT (FILENAME, 10)" does not return all the results.

 USE MyDatabase SELECT x.DWGNO AS X_DWGNO, y.DWGNO AS Y_DWGNO, x.FILENAME AS X_FILENAME y.FILENAME AS Y_FILENAME x.DWGTITLE, x.REV, x.PROJECT FROM dbo.DocShare x -- want all the files from the left table... I think LEFT JOIN dbo.DocShare y ON LEFT(FILENAME LEN(CHARINDEX('.', FILENAME 1))) = LEFT(FILENAME, 10) 

Also tried something different based on a similar post, but it doesn't work either.

 USE MyDatabase SELECT X.E_DWGNO, y.DWGNO AS Y_DWGNO, x.FILENAME AS X_FILENAME y.FILENAME AS Y_FILENAME x.DWGTITLE, x.REV, x.PROJECT FROM dbo.DocShare x WHERE EXISTS(SELECT x.FILENAME FROM dbo.DocShare WHERE x.FILENAME = LEFT(y.FILENAME LEN(CHARINDEX('.', y.FILENAME, 0)))) ORDER BY y.FILENAME 

Answer 1

try it

Sql fiddle

 select f2.Filename,f1.DWGNO,f1.Project from File1 f2 left join File1 f1 on f2.Filename like f1.Filename+'%' where f1.DWGNO != '' 

Answer 2

First you want to get the base files or those lines where DWGNO IS NOT NULL . Then get the revisions ( DWGNO IS NULL ) and execute JOIN in the base files:

SQL Fiddle

 WITH CteBase AS ( SELECT * FROM Tbl WHERE DWGNO IS NOT NULL ), CteRev AS( SELECT t.FileName, DWGNO = cb.DWGNO, Project = cb.Project FROM Tbl t INNER JOIN CteBase cb ON t.FileName LIKE cb.FileName + '%' WHERE t.DWGNO IS NULL ) SELECT * FROM CteBase UNION ALL SELECT * FROM CteRev ORDER BY FileName 

Answer 3

Using% like this can lead to incorrect data if there is a file name, for example, 523510, since it is 52351%. Try below

 USE MyDatabase SELECT x.DWGNO AS X_DWGNO, y.DWGNO AS Y_DWGNO, x.FILENAME AS X_FILENAME y.FILENAME AS Y_FILENAME x.DWGTITLE, x.REV, x.PROJECT FROM dbo.DocShare x -- want all the files from the left table... I think LEFT JOIN dbo.DocShare y ON left(y.[FileName],PATINDEX('%[^0-9]%', y.[FileName])-1) = x.[FILENAME] 

Answer 4

I assume that internal queries are scalar.

It mainly uses patindex() to search for a character without numbers. (I think I have rights to the functions.) We really do not need to update rows that do not include one of them. For those that we need, we need to look for a string that has the corresponding prefix as the full file name. This prefix is ​​all characters before the return value of patindex() .

 update dbo.DocShare set DWGNO = ( select DWGNO from dbo.DocShare as ds where ds.FILENAME = left( dbo.DocShare.FILENAME, patindex('%[^0-9]%', dbo.DocShare.FILENAME + '_') - 1 ) ), PROJECT select PROJECT from dbo.DocShare as ds where ds.FILENAME = left( dbo.DocShare.FILENAME, patindex('%[^0-9]%', dbo.DocShare.FILENAME + '_') - 1 ) ) where patindex('%[0-9]%', FILENAME + '_') > 0