-4

I want different value form given example

sample string :

Column1
---------
6A
6B
6C
7A
7B
7C

Output

Column1   Column2
---------------------
6           A
6           B
6           C
7           A
7           B
7           C

Is there any way Please help

Taryn
  • 242,637
  • 56
  • 362
  • 405
Yagnesh84
  • 157
  • 1
  • 4
  • 19

3 Answers3

3

You can use PATINDEX for that:

--DROP TABLE dbo.MyTbl;

CREATE TABLE dbo.MyTbl(column1 VARCHAR(200));
INSERT INTO dbo.MyTbl(column1)
VALUES
 ('123ab'),
 ('xxx'),
 ('13'),
 (''),
 ('4f');


SELECT LEFT(Column1,PATINDEX('%[^0-9]%',Column1+'x')-1), 
       SUBSTRING(Column1,PATINDEX('%[^0-9]%',Column1+'x'),LEN(Column1))
FROM dbo.MyTbl;

The +'x' makes sure it can handle cases where there are no characters after the numbers.

Sebastian Meine
  • 11,260
  • 29
  • 41
0

If you have a maximum number length, then you can do this pretty directly as:

select (case when numlen > 0 then cast(left(col1, numlen) as int) else 0 end) as column1,
       substring(col1, numlen+1, 100) as column2
from (select col1,
             (case when isnumeric(left(col1, 10)) = 1 then 10
                   when isnumeric(left(col1, 9)) = 1 then 9
                   when isnumeric(left(col1, 8)) = 1 then 8
                   when isnumeric(left(col1, 7)) = 1 then 7
                   when isnumeric(left(col1, 6)) = 1 then 6
                   when isnumeric(left(col1, 5)) = 1 then 5
                   when isnumeric(left(col1, 4)) = 1 then 4
                   when isnumeric(left(col1, 3)) = 1 then 3
                   when isnumeric(left(col1, 2)) = 1 then 2
                   when isnumeric(left(col1, 1)) = 1 then 1
                   else 0
              end) as Numlen
      from t
    ) t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    it if fairly well documented that isnumeric has some "problems" http://stackoverflow.com/questions/7567804/sql-isnumeric-not-working or google `isnumeric problems` – KM. Mar 22 '13 at 20:32
0

Assuming it starts with numbers..

DECLARE @tab TABLE(Col VARCHAR(50))
INSERT INTO @tab 
VALUES('6A')
,('65BC'),('654CBA'),('7654ABCD')
,('76543BCDEF'),('765432CDEFGG')

SELECT *
FROM @tab

SELECT Col
,SUBSTRING(Col,1,(p0.num1-1)) AS Col1
,SUBSTRING(Col,p0.num1,LEN(Col)) AS Col2
FROM @tab
CROSS APPLY (
    SELECT 
    PATINDEX('%[^0-9]%',Col) AS num1)p0
Narsimha
  • 184
  • 4