Substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Substring

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-09-10 : 05:31:37

i have a list of data as below.

ColumnA
Hotel A146 10/25/2014
Hotel B149 9/10/2014
Hotel A19 11/7/2013
Hotel A199 12/31/2014

I would like to just grab hotel id to put in new field.
I tried select columnA, left(columnA,11) newField from tableA

But for those which have lesser count will include the date in.

Only would like to pull the hotel id.

Hotel A146
Hotel B149
Hotel A19
Hotel A199

what shall i use?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-10 : 08:13:23

[code]SELECT LEFT(ColumnA,CHARINDEX(' ',ColumnA+' ')-1);[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-10 : 08:55:30

[code]DECLARE @Sample TABLE
(
ColumnA VARCHAR(100) NOT NULL
);

INSERT @Sample
(
ColumnA
)
VALUES ('Hotel A146 10/25/2014'),
('Hotel B149 9/10/2014'),
('Hotel A19 11/7/2013'),
('Hotel A199 12/31/2014');

-- SwePeso
SELECT ColumnA,
SUBSTRING(ColumnA, 1, LEN(ColumnA) - CHARINDEX(' ', LTRIM(REVERSE(ColumnA) + ' ')))
FROM @Sample;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-09-11 : 02:58:23

I tried this method:
SELECT LEFT(ColumnA,CHARINDEX(' ',ColumnA+' ')-1);

I notice some of the data has space which I believe is a tab.
How to solve this issue?

ColumnA
Hotel A19 1/20/2014
Hotel A199 10/12/2014
Hotel A99 9/25/2014
Hotel B124 11/25/2014

I get this error:
Invalid length parameter passed to the LEFT or SUBSTRING function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources