December 8, 2015 at 3:41 pm
I am new to SQL am using SQL 2012, I'm trying to search a NVARCHAR column that has both Decimal and Alpha-numeric numbers, I can
Pull the information but all I want to do is pull the decimal numbers. and be able to change the scrip as to only pull Alpha-numeric number not the decimal number if I need to.
this is what i have tried
SELECT *
from Table_Name
WHERE RLDES = '84' and TXID NOT LIKE ['a-f']
I get both sets of numbers, if i change LIKE ['0-9'] that still does not work
TXID length is 4, RLDES is 2
All i want is to pull 5268 not 52E0
I know is looks easy for a lot of you but i'm trying to learn. Thank you all for your help
December 8, 2015 at 3:53 pm
Something like this?
CREATE TABLE#alpha_numerics(alpha_numeric VARCHAR(30))
INSERT INTO#alpha_numerics
VALUES ('234'),
('23f5'),
('7689'),
('Hey!'),
('58-Fr')
SELECT*
FROM#alpha_numerics
WHEREalpha_numericNOT LIKE '%[^0-9]%'
DROP TABLE#alpha_numerics
Cheers!
December 9, 2015 at 8:08 am
Jacob
Thank you so much, it worked great, I changed the '%[^0-f]%' to this to pull only Alpha-Numeric numbers it also worked geat.
Again thank you.
December 9, 2015 at 8:30 am
Depending on the collation, that might not work correctly. Try this:
CREATE TABLE#alpha_numerics(alpha_numeric VARCHAR(30));
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
INSERT INTO#alpha_numerics
SELECT CHAR(n)
FROM cteTally
SELECT*, ASCII(alpha_numeric) n
FROM#alpha_numerics
WHEREalpha_numericNOT LIKE '%[^0-9a-fA-F]%' COLLATE Latin1_General_Bin
ORDER BY n
DROP TABLE#alpha_numerics
BTW, I'm assuming that by alpha-numeric, you mean hexadecimal values.
December 9, 2015 at 8:41 am
Jacob
looks like i posted that a little early it didn't work the alpha-numeric part.
SELECT*
FROMBOOKTEMP1
WHERE TXIDNOT LIKE '%[^0-f]%' and RLDES = '84'
I have changed the NOT LIKE to '%[^a-f]%' and it only pulled 21 records and there should have been thousands of records.
there are a total of 4477903 records in the table.
December 9, 2015 at 8:48 am
It could be the issue Luis pointed out, or it could be something else, like your data not matching what we're guessing it looks like.
If you could provide a sample of the data you're working with like I did in my original post (CREATE TABLE commands, INSERTs for the data) then we'll be better able to see what's going on. Check out this link for more info on providing workable sample data: http://www.sqlservercentral.com/articles/Best+Practices/61537/.
If it's not what Luis pointed out, then I would guess that the values not returned by the query contain characters other than 0-9 and a-f (maybe there are hyphens, maybe the hex values are prefixed with 0x, etc.)
If you provide us with sample data and expected results, we should be able to get it cleared up pretty quickly.
Cheers!
December 9, 2015 at 9:19 am
I hope this helps
CREATE TABLE [dbo].[BOOKTEMP1](
[SERIALNO] [int] IDENTITY(1,1) NOT NULL,
[RLDES] [nvarchar](10) NULL,
[TXID] [nvarchar](10) NULL,
[COMMSNO] [nvarchar](4) NULL,
[SYSNO] [nvarchar](4) NULL,
[ACCT] [nvarchar](12) NULL,
[DLR#] [nvarchar](12) NULL,
[CUST_NAME] [nvarchar](60) NULL,
[CLASSCODE] [nvarchar](5) NULL,
[COMMDATE] [nvarchar](11) NULL,
[DECOMDATE] [nvarchar](11) NULL,
[ACCT_STATE] [nvarchar](12) NULL,
[TXIDNUM_TYPE] [nvarchar](15) NULL,
[HYPERLINK] [nvarchar](100) NULL,
CONSTRAINT [PK_BOOKTEMP1] PRIMARY KEY CLUSTERED
(
[SERIALNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The only fields that i need to use to pull the data are RLDES, TXID, and CUST_NAME. I would order by TXID
Data would be like this.
RLDESTXIDCOMMSNO SYSNO ACCT DLR# CUST_NAME
84 0002 NULL NULL 84000002 00998 *** PRE_ALLOCATED_NUMBER ***
84 0005 NULL NULL 84000005 00230 *** PRE_ALLOCATED_NUMBER ***
84 000D NULL NULL 8400000D 01111 *** PRE_ALLOCATED_NUMBER ***
84 000E NULL NULL 8400000E 00172 *** PRE_ALLOCATED_NUMBER ***
84 0011 NULL NULL 84000011 IA1203 *** PRE_ALLOCATED_NUMBER ***
I hope this is a better understanding, I would not want to pull record 3 and 4 from the table, and at some time later i might have to only pull records like 3 and 4 only.
December 9, 2015 at 9:26 am
I hope this helps
CREATE TABLE [dbo].[BOOKTEMP1](
[SERIALNO] [int] IDENTITY(1,1) NOT NULL,
[RLDES] [nvarchar](10) NULL,
[TXID] [nvarchar](10) NULL,
[COMMSNO] [nvarchar](4) NULL,
[SYSNO] [nvarchar](4) NULL,
[ACCT] [nvarchar](12) NULL,
[DLR#] [nvarchar](12) NULL,
[CUST_NAME] [nvarchar](60) NULL,
[CLASSCODE] [nvarchar](5) NULL,
[COMMDATE] [nvarchar](11) NULL,
[DECOMDATE] [nvarchar](11) NULL,
[ACCT_STATE] [nvarchar](12) NULL,
[TXIDNUM_TYPE] [nvarchar](15) NULL,
[HYPERLINK] [nvarchar](100) NULL,
CONSTRAINT [PK_BOOKTEMP1] PRIMARY KEY CLUSTERED
(
[SERIALNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The only fields that i need to use to pull the data are RLDES, TXID, and CUST_NAME. I would order by TXID
Data would be like this.
RLDESTXIDCOMMSNO SYSNO ACCT DLR# CUST_NAME
84 0002 NULL NULL 84000002 00998 *** PRE_ALLOCATED_NUMBER ***
84 0005 NULL NULL 84000005 00230 *** PRE_ALLOCATED_NUMBER ***
84 000D NULL NULL 8400000D 01111 *** PRE_ALLOCATED_NUMBER ***
84 000E NULL NULL 8400000E 00172 *** PRE_ALLOCATED_NUMBER ***
84 0011 NULL NULL 84000011 IA1203 *** PRE_ALLOCATED_NUMBER ***
I hope this is a better understanding, I would not want to pull record 3 and 4 from the table
December 9, 2015 at 9:29 am
cswittmaack (12/9/2015)
I would not want to pull record 3 and 4 from the table, and at some time later i might have to only pull records like 3 and 4 only.
Why? What's the rule for that? Those are valid hexadecimal values.
December 9, 2015 at 9:50 am
Luis
I did not create the table, I know they are hexadecimal numbers.
All I want to do is pull the records that are Decimal numbers not the hexadecimal numbers.
December 9, 2015 at 9:57 am
cswittmaack (12/9/2015)
LuisI did not create the table, I know they are hexadecimal numbers.
All I want to do is pull the records that are Decimal numbers not the hexadecimal numbers.
Just use the condition given by Jacob in his first post.
December 9, 2015 at 10:07 am
Luis
I did use the code and it did work. I was just trying to change it to pull only the hexadecimal numbers in case they ask for them.
I am very sorry if I was misleading anyone. this is the first time i have posted anything, I try to figure it out before I ask anyone.
December 9, 2015 at 10:41 am
No problem. Just try to be clear on what you need as much as possible.
We get confused if you suddenly change the requirements without explaining the reason or saying it won't work for something different than the problem originally presented.
In my signature you can find an article on the best way how to get help. You could also check this article: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply