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 5 (of 5 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy