March 19, 2013 at 1:02 pm
Hi,
I am trying to import into SQL Server 2008 R2 a list of strings as follows:
=KET+N.207-13-F4001
=KET+DG014-13-F4011
=KET+RE002-36-MV009
I need to split the strings so they are inserted into separate fields. One of the substring statements is:
'SUBSTRING(xlCode; 15; 2) [if the second position is a number, and]
'SUBSTRING(xlCode; 15; 1) [if the second position is anything else]
AS GroupNo
My experience with T-SQL is just not enough to figure this one out. Can you tell me how the statement should be set up?
Thanks in advance for any help.
Maarten
March 19, 2013 at 2:00 pm
Hi Maarten
Not sure if this is what you want, but you could try
;with testdata as(
select *
from (values ('=KET+N.207-13-F4001'),('=KET+DG014-13-F4011'),('=KET+RE002-36-MV009')) as MD (value)
)
select value
,case
when substring(value,16,1) like '[0-9]' then
substring(value,15,2)
else
substring(value,15,1)
end as groupno
from testdata
March 20, 2013 at 2:32 pm
Hi MickyT,
Thanks for your help.
This is what I needed, and I got there with your reply.
CASE WHEN substring(xlCode, 16, 1) LIKE '[0-9]' THEN substring(xlCode, 15, 1) ELSE substring
(xlCode, 15, 2) END AS GroupNo
, CASE WHEN substring(xlCode, 16, 1) LIKE '[0-9]' THEN substring(xlCode, 16, 4) ELSE substring
(xlCode, 17, 3) END AS SeqNo
March 20, 2013 at 2:38 pm
Glad to help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply