Someone please help to write query for below screnaio.
I want to fetch only first 6 numeric values from varchar column using below conditions.
Conditions are,
1. Substring only first 6 numeric.
2. If any substring output has alphabet ignore the alphabets.

Tablename: Sys_tablename
Columnname: Sys_columnname

Select Sys_columnname from Sys_tablename;
Go

Sys_columnname:
================
1234564567890
ABCDEFGHIJKLMNOPQRSTU
123456AABCDEFGH
123ABCDEFGHIJKL12
12345ASDFASDFEA123
123456789ASDFASDASDF
12345SDFASDFASDF123

Output would be like below:
======================
123456
NULL (ignore)
123456
123
12345
123456
12345

Can I ask why you're doing this or is it just coursework?

c'mon MBK, its easy, they are storing multiple entities in the same column and not sure that they should fix it or not.

I was going to wait for a reply before lecturing them on their design (or their table naming). Looking at the data though I'd guess it's coursework.

MBK

You can do like this(Only digit and alp).

Select case patindex('%[A-Z]%',upper(Sys_columnname))>6 then substring(Sys_columnname,1,6)
else
substring(Sys_columnname,1,patindex('%[A-Z]%',upper(Sys_columnname))) from Sys_tablename

Hi MBK,
I got this doubt,as part of self learning execise on SYBASE database. If u have any idea on this kindly share with me.

thanks hobblylu.

But patindex is not working.

Sorry for upper sql. It's wrong.
Right SQL is:
Select upper(Sys_columnname),case when patindex('%[A-Z]%',upper(Sys_columnname))>6 then substring(Sys_columnname,1,6)
when patindex('%[A-Z]%',upper(Sys_columnname))=0 then substring(Sys_columnname,1,6)
else
substring(Sys_columnname,1,patindex('%[A-Z]%',upper(Sys_columnname))-1) end from Sys_tablename
1234564567890
123456
ABCDEFGHIJKLMNOPQRSTU
NULL
123456AABCDEFGH
123456
123ABCDEFGHIJKL12
123
12345ASDFASDFEA123
12345
123456789ASDFASDASDF
123456
12345SDFASDFASDF123
12345

thanks a lot hobblylu.Its working ....