Solved

SQL: IsNumeric

Posted on 2014-10-27
3
151 Views
Last Modified: 2014-10-27
I have a column called TaxID which is a VARCHAR.
I need to test if it starts with 2 digits and a dash. What is the best way to do that?

SELECT CASE WHEN CI.TaxID like '##-%' THEN 1 ELSE 2 END

This would be great, if there are wildcards for numbers.
0
Comment
Question by:pzozulka
  • 2
3 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
ID: 40406927
select 
case when isnumeric(left('15-asdff-b', 2)) =1 and charindex('-', '15-asdff-b') = 3
then 1 else 0 end

Open in new window

replace the 15-asdff-b with your column name.  Will return 1 if the left 2 digits are numeric followed by a dash else 0.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40406939
This worked for me.
CREATE TABLE #tmp (value varchar(10))

INSERT INTO #tmp (value) 
VALUES('09-banana'), ('58 Mustang'), ('BR-549'), ('67-omaha'), ('R2-D2')

SELECT value, 
	CASE WHEN ISNUMERIC(LEFT(value, 1)) = 1 
		AND ISNUMERIC(SUBSTRING(value, 2, 1)) = 1 
		AND SUBSTRING(value, 3, 1) = '-' THEN 'true' ELSE 'false' END
FROM #tmp

Open in new window

I tried using Regular Expressions, which would probably be less code, but no love.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40406957
Figured out the regular expression, but Kyle beat me to it..
CREATE TABLE #tmp (value varchar(10))

INSERT INTO #tmp (value) 
VALUES('09-banana'), ('58 Mustang'), ('BR-549'), ('67-omaha'), ('R2-D2')

SELECT value,
   CASE WHEN value  LIKE '[0-9][0-9]-%' THEN 'true' ELSE 'false' END
FROM #tmp

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL - MAX of multiple date columns in a SINGLE Row 4 29
SQL query and VBA 5 41
TSQL convert date to string 4 34
SQL Server Configuration Manager WMI Error 11 16
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question