How to remove some characters from a string in SQL Server

yinyang2...

SOLVED CLOSED ACTIVE ANSWERED
User: yinyang205
Posted: on Mar 09, 2017 09:22 AM
Forum: SQL Server
Answer: 1
Views: 2612

Hi All,

In my case, I would like to drop anything after the date (YYYY-MM-DD).

I tried using REPLACE ([ColumnName], 'Apple(s)', '') but that only remove the part 'Apple(s)' not the number before it.

My data looks like this:

1 2000-10-15 2 Apple(s) 1 2011-08-12 104 Apple(s);1 1985-06-14 14 Apple(s);1 2004-07-18 25 Apple(s);

How should I query this so I am left with the following: 1 2000-10-15 1 2011-08-12;1 1985-06-14;1 2004-07-18;

Thanks so much.

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Mar 09, 2017 09:25 AM
Report

Hi yinyang205,

Refer the below test query.

SQL

DECLARE @Test AS TABLE([ColumnName] VARCHAR(100))
INSERT INTO @Test VALUES('1 2000-10-15 2 Apple(s)')
INSERT INTO @Test VALUES('1 2011-08-12 104 Apple(s)')
INSERT INTO @Test VALUES('1 1985-06-14 14 Apple(s)')
INSERT INTO @Test VALUES('1 2004-07-18 25 Apple(s)')
SELECT [ColumnName]
       ,SUBSTRING([ColumnName],0,CHARINDEX(' ',[ColumnName],3)) AS Output 
FROM @Test

Output

ColumnName	Output
1 2000-10-15 2 Apple(s)	1 2000-10-15
1 2011-08-12 104 Apple(s)	1 2011-08-12
1 1985-06-14 14 Apple(s)	1 1985-06-14
1 2004-07-18 25 Apple(s)	1 2004-07-18

How to remove some characters from a string in SQL Server

Company

Explore

Follow us

Contact Us