June 15, 2005 at 5:49 am
I have a basic question. I'm querying a column that contains both character and numeric data. I want to exclude numeric data but the only way I can do this is by the following.
SELECT Column1, Column2
FROM mytable
WHERE Column1 Not Like '1%'
AND Column1 Not Like '2%'
AND Column1 Not Like '3%'
AND Column1 Not Like '4%'
AND Column1 Not Like '5%'
AND Column1 Not Like '6%'
AND Column1 Not Like '7%'
AND Column1 Not Like '8%'
AND Column1 Not Like '9%'
AND Column1 Not Like '0%'
Isn't there a wildcard for numeric values that I could use rather than have a line for each number?
Thanks,
Brian
June 15, 2005 at 5:57 am
Yep.
LIKE '%[0-9]%'
June 15, 2005 at 6:00 am
Works like a charm and makes coding more simple!
Thanks!
Brian
June 15, 2005 at 6:09 am
Or you could use
where isnumeric(column1) = 0
Cheers
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 15, 2005 at 6:24 am
It is not could Phil you should or must use ISNUMERIC
SET NOCOUNT ON
DECLARE @myTable TABLE
(
myNumChar VARCHAR(10)
)
INSERT @myTable VALUES('111111')
INSERT @myTable VALUES('1AAA1')
INSERT @myTable VALUES('AA11AA')
INSERT @myTable VALUES('111A11')
INSERT @myTable VALUES('2AAAAA')
INSERT @myTable VALUES('AAAAA2')
INSERT @myTable VALUES('222222')
SELECT * FROM @myTable WHERE myNumChar NOT LIKE '%[0-9]%' -- No Result
/* You should/must use ISNUMERIC to get the expected 5 rows */
SELECT * FROM @myTable WHERE ISNUMERIC(myNumChar) = 0
Regards,
gova
June 15, 2005 at 7:10 am
If your calling it form various procedures, stick it in a functon.
June 16, 2005 at 12:30 am
If you need Alpha or Numeric from a mixed string:
CREATE FUNCTION fn_ConvAlpha
(
@String varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
-- Strip non-Alpha characters
DECLARE @Cnt int, @Lgth int, @Temp varchar(8000), @C char(1)
SELECT @Temp='', @Lgth=LEN(@String), @Cnt=1
WHILE @Lgth >= @Cnt
BEGIN
SET @C = SUBSTRING(@String,@Cnt,1) -- character to test
IF (ASCII(@C)>64 AND ASCII(@C)<91) -- is A-Z character
OR (ASCII(@C)>96 AND ASCII(@C)<123) -- is a-z character
OR (ASCII(@C)=32 AND LEN(@Temp+'|')=LEN(@Temp)+1) -- is a single space character
SET @Temp=@Temp+@C
SET @Cnt = @Cnt +1
END
SET @Temp = LTRIM(RTRIM(@Temp))
--PRINT 'String: '''+CONVERT(varchar,@String)+''' Converted: '''+CONVERT(varchar,@Temp)+''' '
RETURN (@Temp)
END
GO
SELECT 'Convert string to Aplha', dbo.fn_ConvAlpha(' ABC-Z$-12,34 5.26 abc=z=\+ ')
GO
CREATE FUNCTION fn_ConvNum
(
@String varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
-- Strip non-Numeric characters
DECLARE @Cnt int, @Lgth int, @Temp varchar(8000), @C char(1), @N char(1), @Sign bit, @Dec bit
SELECT @Temp='', @Lgth=LEN(@String), @Cnt=1, @Sign=0, @Dec =0
WHILE @Lgth>=@Cnt
BEGIN
SET @C = SUBSTRING(@String,@Cnt,1) -- character to test
SET @N = SUBSTRING(@String,@Cnt+1,1) -- next character
IF (ASCII(@C)>47 AND ASCII(@C)<58) -- IsNumeric
OR (ASCII(@C)=46 AND @Dec=0) -- one decimal character
OR (ASCII(@C)=45 -- negative sign
AND (@Sign=0 AND LEN(@Temp)=0) -- leading negative sign
AND (ASCII(@N)>47 AND ASCII(@N)<58)) -- next character IsNumeric
SET @Temp=@Temp+@C
IF ASCII(@C)=46
SET @Dec=1
IF ASCII(@C)=45
SET @Sign=1
SET @Cnt = @Cnt +1
END
SET @Temp = LTRIM(RTRIM(@Temp))
--PRINT 'String: '''+CONVERT(varchar,@String)+''' Converted: '''+CONVERT(varchar,@Temp)+''' '
RETURN (@Temp)
END
GO
SELECT 'Convert string to Numeric', dbo.fn_ConvNum('ABC$-12,3-45.26.1% abc=\+-')
Andy
June 16, 2005 at 6:58 am
Be careful using IsNumeric() in cases like this. IsNumeric() will return 1 (true) for strings which contain numbers in scientific notation, such as the following:
IF IsNumeric('333D33') = 1
PRINT 'Value is a number'
ELSE
PRINT 'NOT a number'
IF IsNumeric('444E11') = 1
PRINT 'Value is a number'
ELSE
PRINT 'NOT a number'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply