Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring with IF or CASE statement Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:55 AM
Points: 5, Visits: 18
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
Post #1432866
Posted Tuesday, March 19, 2013 2:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:10 PM
Points: 1,066, Visits: 3,136
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

Post #1432892
Posted Wednesday, March 20, 2013 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:55 AM
Points: 5, Visits: 18
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
Post #1433487
Posted Wednesday, March 20, 2013 2:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:10 PM
Points: 1,066, Visits: 3,136
Glad to help
Post #1433492
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse