I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
7  4942 
I looked at ISNUMERIC first but I don't think thats what you are after, but
this will work...
set nocount on
declare @test table (
MyData nvarchar(50) not null
)
insert @test values ( 'thisoneok' )
insert @test values ( 'has123numbers' )
declare @numbers table (
digit char(1) not null
)
insert @numbers values( '0' )
insert @numbers values( '1' )
insert @numbers values( '2' )
insert @numbers values( '3' )
insert @numbers values( '4' )
insert @numbers values( '5' )
insert @numbers values( '6' )
insert @numbers values( '7' )
insert @numbers values( '8' )
insert @numbers values( '9' )
select *,
has_numbers = case when exists (
select *
from @numbers n
where len( replace( t.MyData, n.digit, '' ) ) <>
len( t.MyData )
) then 'Y' else 'N' end
from @test t
--
Tony Rogerson
SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant http://sqlserverfaq.com - free video tutorials
<ja*****@gmail. comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
>I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
Thanks for your reply.
Actually i was looking for something like ISNUMERIC. I just wanted to
pull out rows that contains only numbers and just eliminate the others
with chars in them.
Here is what i tried so far....
Select * from Mytable where ISNUMERIC(subst ring(COL2,2,10) )
Assuming data field looks like this
COL2
A123X456
A45687E
I only want to do the check on portion of the string so i used
substring to take out the part i wanted to test against ISNUMERIC.
It shold work, but for some reason im getting an error msg. Any idea
what am i doing wrong here?
Thanks!
Tony Rogerson wrote:
I looked at ISNUMERIC first but I don't think thats what you are after, but
this will work...
set nocount on
declare @test table (
MyData nvarchar(50) not null
)
insert @test values ( 'thisoneok' )
insert @test values ( 'has123numbers' )
declare @numbers table (
digit char(1) not null
)
insert @numbers values( '0' )
insert @numbers values( '1' )
insert @numbers values( '2' )
insert @numbers values( '3' )
insert @numbers values( '4' )
insert @numbers values( '5' )
insert @numbers values( '6' )
insert @numbers values( '7' )
insert @numbers values( '8' )
insert @numbers values( '9' )
select *,
has_numbers = case when exists (
select *
from @numbers n
where len( replace( t.MyData, n.digit, '' ) ) <>
len( t.MyData )
) then 'Y' else 'N' end
from @test t
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ja*****@gmail. comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
Thanks for your reply.
Actually i was looking for something like ISNUMERIC. I just wanted to
pull out rows that contains only numbers and just eliminate the others
with chars in them.
Here is what i tried so far....
Select * from Mytable where ISNUMERIC(subst ring(COL2,2,10) )
Assuming data field looks like this
COL2
A123X456
A45687E
I only want to do the check on portion of the string so i used
substring to take out the part i wanted to test against ISNUMERIC.
It shold work, but for some reason im getting an error msg. Any idea
what am i doing wrong here?
Thanks!
Tony Rogerson wrote:
I looked at ISNUMERIC first but I don't think thats what you are after, but
this will work...
set nocount on
declare @test table (
MyData nvarchar(50) not null
)
insert @test values ( 'thisoneok' )
insert @test values ( 'has123numbers' )
declare @numbers table (
digit char(1) not null
)
insert @numbers values( '0' )
insert @numbers values( '1' )
insert @numbers values( '2' )
insert @numbers values( '3' )
insert @numbers values( '4' )
insert @numbers values( '5' )
insert @numbers values( '6' )
insert @numbers values( '7' )
insert @numbers values( '8' )
insert @numbers values( '9' )
select *,
has_numbers = case when exists (
select *
from @numbers n
where len( replace( t.MyData, n.digit, '' ) ) <>
len( t.MyData )
) then 'Y' else 'N' end
from @test t
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<ja*****@gmail. comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
(ja*****@gmail. com) writes:
I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
Not really sure what you are looking for, but this query returns all
rows with digits only in COL2.
SELECT col2
FROM tbl
WHERE col2 NOT LIKE '%[^0-9]%'
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
I think this works for me...i tried this with success so far...
select * from Table where substring(COL2, 2,6) Like '%[0-9]')
As i know a char [A-Z] will only appear at position 7 of the string. It
only returns values that are ending with a digit.
Erland Sommarskog wrote:
(ja*****@gmail. com) writes:
I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??
TBALE Example
COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220
Not really sure what you are looking for, but this query returns all
rows with digits only in COL2.
SELECT col2
FROM tbl
WHERE col2 NOT LIKE '%[^0-9]%'
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: todd |
last post by:
here is a search tool SP I wrote.
How many times have you wanted to search all your Stored procs or views (in
a
database) for a keyword but couldn't!?
Well now you can! THis can makes life a lot easier for developers.
Would you email me and let me know which part of the world my code is
running in?
|
by: pembed2003 |
last post by:
Hi all,
I asked this question in the C group but no one seems to be interested
in answering it. :-( Basically, I wrote a search and replace function
so I can do:
char source = "abcd?1234?x";
char search = '?';
char* replace = "***";
char* result = search_and_replace(source,search,replace);
|
by: Dave Townsend |
last post by:
Hi,
Can anybody help me with the following piece of code?
The purpose behind the code is to parse HTML files, strip out
the tags and return the text between tags. This is part of a larger
application which will perform "searches" for text values in a
directory of html files, trying to match only the non-tagged text
in the documents.
|
by: pembed2003 |
last post by:
Hi all,
I need to write a function to search and replace part of a char*
passed in to the function. I came up with the following:
char* search_and_replace(char* source,char search,char* replace){
char* result;
size_t l = strlen(source), r = strlen(replace), i;
int number_of_replaces = 0;
for(i = 0; i < l; i++){
if(source == search)
|
by: heath |
last post by:
i'm building a website for a band and each section of the site, i want
to have google style searches. mainly, for the news and tour section.
i'm using mysql 4.0.26 and php 4.0 for all web content. below is the
query i created for the tour search (this example looks for 3 search
items). php builds this dynamically but for each search term, there is
an extra statement in the where clause. additionally, there are 5
table joins but i left...
| | |
by: visionstate |
last post by:
Hi there,
Is there a way of searching a sub-form (reading from a query) without
writing what is exactly contained in the query field.
For example if I had a 'Finance Manager' in the query but just wanted
to type 'Finance' in the text box to search or 'Admin Manager' in the
query and just wanted to search 'Admin' in the text box.
How would I do it so when I searched just 'Admin' or 'Finance', 'Admin
Manager' and 'Finance Manager' appeared...
|
by: Eric |
last post by:
Hi: I have two files. I search pattern ":" from emails text file and
save email contents into a database. Another search pattern " field is blank. Please try again.",
vbExclamation + vbOKOnly
Me.txtEmail.SetFocus
Exit Sub
End If
Me.txtStatusBar.Value = "Parsing..."
strEmail = Me.txtEmail.Value
|
by: fienen |
last post by:
I am working on a script to handle a search query. In some instances,
the query could come through as "isbn:%20#############" (where %20 is
an encoded space and the colon is optional). Basically I want to
strip off the ISBN portion and leave just the numbers if that is the
case.
Orignally I was trying
$value = $_GET;
if (preg_match("isbn:?%20", $value))
{
|
by: cglewis03 |
last post by:
Hello,
I am trying to build a search form with several different options to choose from. Currently it is set up to open within the same window if a single option is selected and open within a frameset if the "ALL" option is selected.
Is there anyway to get the results to open in new windows?
So if the user were to select the "ALL" option, is it possible to open 3 new windows with each result displaying?
Here is my code:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
