September 13, 2014 at 10:49 pm
i have a list of data as below.
ColumnA
Hotel A146 10/25/2014 hotel location Perth
Hotel B149 9/10/2014 hotel location London
Hotel A19 11/7/2013 hotel location Perth
Hotel A199 12/31/2014 hotel location Perth
I would like to just grab hotel id to put in new field.
I tried this method:
SELECT LEFT(ColumnA,CHARINDEX(' ',ColumnA+' ')-1)
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?
I also notice some of the data has space which I believe is a tab.
Is it applying char(6) will solve the tab issue?
How to apply substring with case when method?
September 13, 2014 at 11:47 pm
Here is a quick solution using the charindex function to locate the boundaries. The first one depends on the first word being consistent, the second one has slightly more flexibility as it allows for differences in the first word.
There are other slightly more elaborate ways but it looks like this will do the job.
😎
USE tempdb;
GO
SET NOCOUNT ON
;WITH SAMPLE_DATA(ColumnA) AS
( SELECT * FROM
(VALUES
('Hotel A146 10/25/2014 hotel location Perth')
,('Hotel B149 9/10/2014 hotel location London')
,('Hotel A19 11/7/2013 hotel location Perth')
,('Hotel A199 12/31/2014 hotel location Perth')
,('Hot. A199 12/31/2014 hotel location Perth')
) AS X(ColumnA)
)
SELECT
SUBSTRING(SD.ColumnA,1,CHARINDEX(' ',SD.ColumnA,7) -1 ) AS FIXED_PART
,SUBSTRING(SD.ColumnA,1,CHARINDEX(' ',SD.ColumnA,(CHARINDEX(' ',SD.ColumnA,1) + 2) -1 )) AS FLEX_PART
,SD.ColumnA
FROM SAMPLE_DATA SD
Results
FIXED_PART FLEX_PART ColumnA
----------- ------------ ------------------------------------------
Hotel A146 Hotel A146 Hotel A146 10/25/2014 hotel location Perth
Hotel B149 Hotel B149 Hotel B149 9/10/2014 hotel location London
Hotel A19 Hotel A19 Hotel A19 11/7/2013 hotel location Perth
Hotel A199 Hotel A199 Hotel A199 12/31/2014 hotel location Perth
Hot. A199 Hot. A199 Hot. A199 12/31/2014 hotel location Perth
September 14, 2014 at 12:22 am
May I know what does the 7 means?
SUBSTRING(SD.ColumnA,1,CHARINDEX(' ',SD.ColumnA,7) -1 ) AS FIXED_PART
September 14, 2014 at 2:22 am
girl_bj (9/14/2014)
May I know what does the 7 means?SUBSTRING(SD.ColumnA,1,CHARINDEX(' ',SD.ColumnA,7) -1 ) AS FIXED_PART
It tells the charindex where to start searching for the character.
😎
Hotel A146 10/25/2014 hotel...
123456789012345678901234567890
^ ^--charindex match
'--charindex start point
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply