Query to Display Where a Column Contains Alphanumeric Data??

Question

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

Vikram Agrawal · Answer

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 Begin Declare @Char Varchar(1)=Substring(@Add@i1); 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 Avanigadda · Answer

Yes Vikram Agrawal

Vikram Agrawal · Answer

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 Avanigadda · Answer

Thnk u Manish Kumar Choudhary and Kunal Vaishya

Kunal Vaishya · Answer

may be it will help you SELECT columnName FROM table where ISNUMERIC(columnName) = 1 use this ISNUMERIC for filtering data.

Manish Kumar Choudhary · Answer

Hi priya Avanigadda  For numeric row select * from where not like '%[^0-9]%' For alphanumeric row select * from where like '%[^0-9]%'