-2

How would I achieve the following in SQL Server?

Data looks like this:

Filename     Yes/No     Key
Abidabo      Yes        92873-987dsfkj80-2002-04-11
Bibiboo      No         20392-208kj48384-2008-01-04

Want it to look like this:

Filename     Yes/No     Key
Abidabo      Yes        92873-987dsfkj80-20020411
Bibiboo      No         20392-208kj48384-20080104

So that the hyphens would disappear ONLY for the dates in the end.

Probs
  • 343
  • 2
  • 6
  • 20
  • People have [already ansered](https://stackoverflow.com/questions/44257621/how-to-take-apart-information-between-hyphens-in-sql-server) this question and explained that you shouldn't use such a schema. You just found another reason why. – Panagiotis Kanavos May 30 '17 at 10:33
  • Why don't you use three separate fields? If you are going to parse the values with each SELECT, why not fix the problem once and for all? – Panagiotis Kanavos May 30 '17 at 10:33
  • 4
    Possible duplicate of [How to take apart information between hyphens in SQL Server](https://stackoverflow.com/questions/44257621/how-to-take-apart-information-between-hyphens-in-sql-server) – Jayvee May 30 '17 at 10:35
  • Modifying the un-normalized field isn't going to help. Each time you try to search for a date, or try to select the date part, you'll have to process the entire table and parse the field *again*. You can't use any indexes. Apart from fixing the schema, it would be better if you tried to create a persisted calculated field with an index. – Panagiotis Kanavos May 30 '17 at 10:41

2 Answers2

0

Use a combination of LEFT, RIGHT and REPLACE string functions.

Query

select *, 
left([key], len([key]) - 10) + replace(right([key], 10), '-', '')
from your_table_name;

You can use it for an update statement as well.

Find a demo here

update [tblFiles]
set [key] = left([key], len([key]) - 10) + replace(right([key], 10), '-', '')
from [your_table_name];

And all key column values should be in the same manner.
I meant, should ends with .........yyyy-mm-dd

Ullas
  • 11,450
  • 4
  • 33
  • 50
0
;With cte([Filename],[Yes/No],[Key])
AS
(
SELECT 'Abidabo','Yes' ,'92873-987dsfkj80-2002-04-11' UNION ALL
SELECT 'Bibiboo','No'  ,'20392-208kj48384-2008-01-04'
)
SELECT [Filename]
    ,[Yes/No]
    ,CONCAT(SUBSTRING([Key],0,LEN([Key])-9)
    ,REPLACE(SUBSTRING([Key], LEN(SUBSTRING([Key], 0, LEN([Key]) - 8)), LEN([Key])), '-', '')) AS ExpectedKey
FROM cte

OutPut

Filename     Yes/No     Key
-------------------------------------------------
Abidabo      Yes        92873-987dsfkj80-20020411
Bibiboo      No         20392-208kj48384-20080104