how to seperate the alphabet and number from an alphanumeric value in database in a sql query in asp.net?

Question

I have an alphanumeric id value like "com33" in an access database column. While retrieving this data in a sql query I want to split "com" and "33" seperately like the .Remove() method in asp.net .

Answer 1

You can do this a number of ways. The easiest is to use regular expressions. VB.NET / C# better supports regular expressions than SQL does, so I'd do this on the programming side.

Dim strInput As String = "code33"

Dim reAlpha As New RegEx("[a-z]{1,}", RegexOptions.IgnoreCase)
Dim reNumeric As New RegEx("[0-9]{1,}", RegexOptions.IgnoreCase)

Dim strAlpha As String = reAlpha.Match(strInput)
Dim strNumeric As String = reNumeric.Match(strInput)

Answer 2

For SQL Server, you could set up a function and call that function to get your value. In the function pass your value and then loop through one character at a time returning what you want.

Here is a quick example you can use as a guide. It loops through and returns only A-Z or a-z in the first loop and numeric in the second. If you want to get other values besides the A-Z, you could just always use the IsNumeric function to get all non-numerics when it returns 0.

Hope this helps.




Declare @StartUpper int,@EndUpper int
Declare @StartLower int,@EndLower int

Select @StartUpper = ascii('A'), @EndUpper = ascii('Z'), @StartLower = ascii('a'), @EndLower = ascii('z')
Declare @SomeValue varchar(100),@OutValue varchar(100),@OutValue2 varchar(100)
Declare @Len int, @loop int
Declare @Work int
set @loop = 1
set @SomeValue = 'A123456789BCDEF'
set @OutValue = ''
set @OutValue2 = ''
Set @len = len(@SomeValue)

while @loop <= @len
begin
set @Work = ascii(Substring(@SomeValue,@loop,1))
-- Bypass characters and return only numeric
if (@Work >= @StartUpper and @Work <= @EndUpper) or (@Work >= @StartLower and @Work <= @EndLower)
set @OutValue = @OutValue + Substring(@SomeValue,@loop,1)

set @loop = @loop + 1
end


set @Loop = 1

while @loop <= @len
begin
-- Bypass characters and return only numeric
if isNumeric(Substring(@SomeValue,@loop,1)) = 1
set @OutValue2 = @OutValue2 + Substring(@SomeValue,@loop,1)

set @loop = @loop + 1
end

select @OutValue,@OutValue2

Answer 3

In SQL statement you can use LEFT() or RIGHT(). In MS-SQL I think it works with substring(). Those 3 are to split portions of data from a field. ie AAA-999999

You can use LEFT(FIELD, 3) to get "AAA"
You can use RIGHT(FIELD, 6) to get "999999"
Substring(Fields, 4,1) to get "-"

Remember that concatenate is a "+" between field names.

Example Select LEFT(FIELD1, 6) + RIGHT(FIELD1,6) As FieldNameX

If you are referring to sorting the data check the CAST() function to convert the data into a different type. If the data is fixed in their positions you can break them with LEFT(), RIGHT(), and SUBSTRING() and sort it any way you like.

i.e. SORT BY LEFT(FIELD1,3) ASC, RIGHT(FIELD1,6) DESC

Hope this helps.

Answer 4

Suggestion:
Be a little more descriptive, by giving an example.