MS

Avinash Vajpayee

11y1.1kLearn iOS Programming

In Ms Sql is it possible to find data in column on the basis of alphanumeric range

A1,A2,A3,A4,A5,A6,A7,..................................................A99999

what is MS Sql Query is

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyaranjan K S11y ago
Hi Avinash,

I have updated the query .

CREATE TABLE dbo.TestTable
(
RowValue varchar(50) NULL
)
INSERT INTO dbo.TestTable (RowValue) VALUES ('A1')
INSERT INTO dbo.TestTable (RowValue) VALUES ('AB1')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A2')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A3')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A11')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A21')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A13')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A15')
INSERT INTO dbo.TestTable (RowValue) VALUES ('B20')
INSERT INTO dbo.TestTable (RowValue) VALUES ('B21')
SELECT RowValue
FROM dbo.TestTable
where RowValue between 'A1' and 'A20'
ORDER BY LEFT(RowValue,PATINDEX('%[0-9]%',RowValue)-1),
CONVERT(INT,SUBSTRING(RowValue,PATINDEX('%[0-9]%',RowValue),LEN(RowValue)))
DROP Table dbo.TestTable

Thanks,
Priyan

Please Mark the suggestion as Answer if You found it useful .
0
Avinash Vajpayee11y ago
what about the query when data fetch between A1 to A20
0
Ravi Kumar11y ago
http://www.codeproject.com/Questions/716861/Convert-Row-Data-Into-Column-in-Sql-Server
0
Priyaranjan K S11y ago
Hi Avinash ,

Please try out this query .
CREATE TABLE dbo.TestTable
(
RowValue varchar(50) NULL
)
INSERT INTO dbo.TestTable (RowValue) VALUES ('A1')
INSERT INTO dbo.TestTable (RowValue) VALUES ('AB1')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A2')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A3')
INSERT INTO dbo.TestTable (RowValue) VALUES ('A11')
INSERT INTO dbo.TestTable (RowValue) VALUES ('B20')
INSERT INTO dbo.TestTable (RowValue) VALUES ('B21')
SELECT RowValue
FROM dbo.TestTable
ORDER BY LEFT(RowValue,PATINDEX('%[0-9]%',RowValue)-1),
CONVERT(INT,SUBSTRING(RowValue,PATINDEX('%[0-9]%',RowValue),LEN(RowValue)))
DROP Table dbo.TestTable

Thanks,
Priyan

Please Mark the suggestion as Answer if You found it useful .
0