Inserting values in the database

Question

Inserting values in the database

anjaliagarwal5

Ten Centuries

Points: 1267
More actions
August 12, 2015 at 10:07 am

#299761

Hi Everyone,
I am trying to insert a value in the table. Sometimes the value has this symbol at the end. I just want insert the entire value except this symbol. The values can be like this that I am insert in the table
Insert into Table1(col1)
Values (12345§ )
The values can be, below is just an example
12345§
3456DER§
5678D
FGR564
I want to insert only these values from the above data
12345
3456DER
5678
FGR564
any help will be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Is there a domain of values at the end? Meaning, do you know the possibilities? Or is it a large set of possible values?

The way to do this is use the RIGHT() function and look for the value. If it's there, then with a CASE statement you can SUBSTRING out the string using LEN() - 1 if the value is there.

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 2

there are lot of values inserting in that table, but this special character will be always at the end if present.

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 3

Is there any way, I can just filter out on alphanumeric values and leave the special characters. I tried this, but not working

SELECT * FROM TestTable

WHERE parameter_no LIKE '%[^a-zA-Z0-9]%'

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

anjaliagarwal5 (8/12/2015)
there are lot of values inserting in that table, but this special character will be always at the end if present.

Probably the easiest way is to use replace.

declare @SomeValue varchar(10) = '12345§'

select @SomeValue

, REPLACE(@SomeValue, '§', '')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

anjaliagarwal5 Ten Centuries Points: 1267 More actions · Answer 5

anjaliagarwal5

Ten Centuries

Points: 1267

August 12, 2015 at 10:46 am

#1819426

Thank you. Never thought of using replace.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

Ah, didn't think of replace. Certainly that can work if the domain of special characters is just the one character, and it never appears within the strings. If it could appear as part of one of the strings in the middle, like this: 12§345§ , then that won't work.

The domain is the possible set of values. Is this special character always the only one? Could there be some other character, or no? It almost seems like this might be a character not in your code page, which would make me worry that some other value might appear, or the underlying data contains different values.

Your LIKE doesn't work because it's matching essentially everything that has alphanumerics in the middle of the string.

I would still lean towards trying to strip off the last character if it matches this character, but if this is only at the end, Sean's solution will work.

The important thing here is to really think about the possible values of what will appear and not get too caught up in a small subset. Don't spin on this for days, but it's worth 20 minutes to really look at the data and possible values.