MS

Question

In Ms Sql is it possible to find data in column on the basis of alphanumeric range like A1A2A3A4A5A6A7..................................................A99999 what is MS Sql Query is

Priyaranjan K S · Answer

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(RowValuePATINDEX('%[0-9]%'RowValue)-1) CONVERT(INTSUBSTRING(RowValuePATINDEX('%[0-9]%'RowValue)LEN(RowValue))) DROP Table dbo.TestTable Thanks Priyan Please Mark the suggestion as Answer if You found it useful .

Avinash Vajpayee · Answer

what about the query when data fetch between A1 to A20

Ravi Kumar · Answer

http://www.codeproject.com/Questions/716861/Convert-Row-Data-Into-Column-in-Sql-Server

Priyaranjan K S · Answer

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(RowValuePATINDEX('%[0-9]%'RowValue)-1) CONVERT(INTSUBSTRING(RowValuePATINDEX('%[0-9]%'RowValue)LEN(RowValue))) DROP Table dbo.TestTable Thanks Priyan Please Mark the suggestion as Answer if You found it useful .