Post reply

sql query for removing records with special characters

  • October 17, 2008 at 7:06 am

    #194763

    Hi there,

    I have a column which contains Id#. Ideally its length should be 10. For which i am using LEN(COL#A)=10.

    However, it also contains numbers such as '123-45-678' . This also has length of 10.

    How can i get rid of it?

    Like as of now, I have

    SELECT col#A FROM table#1

    whereCol#A <> ''

    AND LEN(col#A)=10.

    I thought of using Clause CONTAINS .. BUT that does not work here. the error message says, table is not full text indexed.

    looking for suggestions,

    Thanks

  • October 17, 2008 at 7:13 am

    #886330

    by "get rid of it" i assume you mean eliminate from the query, and not delete from the table...

    you want to use the LIKE operator: LIKE '%-%' means a dash appearing anywhere int he column.

    SELECT col#A FROM table#1

    where Col#A <> ''

    AND LEN(col#A)=10

    AND col#A NOT LIKE '%-%' --no dashes allowed?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 17, 2008 at 7:15 am

    #886333

    Yes. I just want to eliminate such records from result set.

    A result set w/o such characters.

    Thanks!

    Damn, i am stupid ...that was easy. never thought of using wildcard

  • October 17, 2008 at 7:19 am

    #886335

    Am not sure what you mean by 'get rid off' but if you want to count just the numbers then use this

    select len( replace('123-45-678','-','') )

  • October 17, 2008 at 7:22 am

    #886339

    By 'get rid off' I meant... eliminating records which contains special characters from result set only.

    NOT from the table.

    Getting lnly those records which do not contain such character and their len being 10

Viewing 5 posts - 1 through 4 (of 4 total)

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