Query to Display Where a Column Contains Alphanumeric Data??

priya Avanigadda

query to display where a column contains alphanumeric data and i want to retrieve both string and numeric data separately into two columns
as
column
...................
abc
efg
123
456

6 Replies

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

Vikram Agrawal11y ago
Well You can use the following Stored Procedure for your requirement

Alter Proc USP_SeprateValues
As
Begin
Declare @NewTable Table(StringAddress Varchar(100),IntAddress Varchar(100))
Declare @Add Varchar(100);
Declare Cur_Val Cursor FOR
Select Address From EmpTest
Open Cur_Val
Fetch Next From Cur_Val INTO @Add;
While(@@FETCH_STATUS=0)
Begin
Declare @Length Int=Len(@Add);
Declare @I Int=1;
Declare @IntAddress Varchar(100)='';
Declare @StringAddress Varchar(100)='';
while(@I<=@Length)
Begin
Declare @Char Varchar(1)=Substring(@Add,@i,1);
If(@Char In ('1','2','3','4','5','6','7','8','9','0'))
Set @IntAddress = @IntAddress + @Char;
Else
Set @StringAddress = @StringAddress + @Char;
Set @I=@I+1;
End
Insert Into @NewTable Values(@StringAddress,@IntAddress);
Fetch Next From Cur_Val INTO @Add;
End
Close Cur_Val
Deallocate Cur_Val
Select * From @NewTable
End

Modify this as per your requirement
Thanks.

Mark This Answer is correct if it is helpful to you
priya Avanigadda11y ago
Yes Vikram Agrawal
Vikram Agrawal11y ago
Hi priya can you clarify one thing that you said in your column values are alphanumeric so it might be like this

Column
abc123
254pqr
mmmm
1234

so do you want to seprate these values in two columns like:

Column 1 Column 2
abc          123
pqr          254
mmmm          NULL
NULL            1234
priya Avanigadda11y ago
Thnk u Manish Kumar Choudhary and Kunal Vaishya
Kunal Vaishya11y ago
may be it will help you,

SELECT columnName FROM table where ISNUMERIC(columnName) = 1

use this ISNUMERIC for filtering data.
Manish Kumar Choudhary11y ago
Hi priya Avanigadda,
For numeric row

select * from where not like '%[^0-9]%'

For alphanumeric row

select * from where like '%[^0-9]%'