Please help on a Column search

  • 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

  • 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!

  • 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.

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • 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!

  • 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.

  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • cswittmaack (12/9/2015)


    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.

    Just use the condition given by Jacob in his first post.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • 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/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply