parsing string values

[DrewConn]

OK, here's what I'm trying to do:

I have two varchar fields, Loan_nbr and Cust_nbr. The values in the Loan_nbr field range from 8 to 16 bytes. Values look like this:
7800728770
7800133610-45839
7800151635-11865
7800735688
7800734959-

The corrasponding values for the cust_nbr are:
1
null
null
001
45445

What I need to do is to pull out everyting in the loan_nbr field before a - and everything after the - will be the cust_nbr.

As the data shows some loan_nbr and cust_nbr values are already seperated, some are combined, some have -'s and some don't. Also the byte size of the loan_nbr field varies, some acct_nbrs are 8 bytes and others are 10 bytes.

Thanks in advance
DrewConn

 

[nra]

As I understand from your message, I think you need the number before "-" for the loan number. Here is how you pull it out. In this example, c1 is the loan_nbr column and table1 is the table name.

SELECT CASE PATINDEX('%-%',c1)
WHEN 0 THEN c1
ELSE SUBSTRING(c1,1,PATINDEX('%-%',c1)-1)
End
FROM table1

 

[nra]

As far as the cust_nbr is concerned, here is the statement.

SELECT CASE PATINDEX('%-%',Loan_nbr)
WHEN 0 THEN ''
ELSE SUBSTRING(Loan_nbr,
PATINDEX('%-%',Loan_nbr)+1,
LEN(Loan_nbr))
+
CASE WHEN Cust_nbr IS NULL THEN ''
ELSE Cust_nbr
FROM Table1

 

[DrewConn]

Thanks I was using a very lengthy case statement. Your way is much more efficient.