how to check the string contains A to Z or 0 to 9 Characters

uppalapati2003 · Post by **uppalapati2003** » Thu Oct 25, 2007 12:00 am

Hi i am getting one string in that i need to check if the string is contains any spcial charecters, if contains i need to convert space for this i need to check the string . string A to Z and 0 to 9 other than this i need to convert space how to do this ? now iam checking the key board special char using of convert function but my reqirement is non key board spcial function also i need to convert space for this how to do the string cmparison with A to Z and 0 to 9 if not convert space
please advise on me in this.

Thanks in Advance

ArndW · Post by **ArndW** » Thu Oct 25, 2007 12:04 am

Please do not double-post the same question several times, it won't help you get an answer any faster. In fact it usually has the opposite effect.

the duplicate post can be found here

Moderator - Please delete this duplicate post

uppalapati2003 · Post by **uppalapati2003** » Thu Oct 25, 2007 12:50 am

I Request for forgiveness posting Double time. can any one hands on this.

Thanks in Advance

balajisr · Post by **balajisr** » Thu Oct 25, 2007 1:17 am

You need to use Char() within Convert Function

E.g To remove new line character:

Code: Select all


Convert(Char(10),' ',Col1)

Delete the other post before anyone replies to that.

ArndW · Post by **ArndW** » Thu Oct 25, 2007 1:43 am

<sigh> this why I requested this thread "die" and even posted a link to the other thread so that people could follow it.

The CONVERT() function is good, but you would need to explicitly enter a very long function for all the non-displayable characters than need to be transposed to space, i.e.

Code: Select all

CONVERT(CHAR(1):CHAR(2):CHAR(3):CHAR(4):CHAR(5):CHAR(6),'      ',In.Column)

. Most efficient would be to create that string in a stage variable and declare it only once, then use it in the CONVERT() function.

uppalapati2003 · Post by **uppalapati2003** » Thu Oct 25, 2007 1:44 am

i am not sure which are all spcial charectors are coming becaiuse of that we need check input string contains A to Z and 0 to 9 otherwise we need put space

balajisr · Post by **balajisr** » Thu Oct 25, 2007 2:11 am

<sigh> this why I requested this thread "die" and even posted a link to the other thread so that people could follow it.

I Apoligize. I thought OP can delete the duplicate post easily since no one has replied to it.

ArndW · Post by **ArndW** » Thu Oct 25, 2007 3:27 am

uppalapati2003 - you've now gotten the answer you need - just type in the 150 or so non-displayable CHAR codes (which you can look up on any ASCII chart) for your CONVERT() statement.

vikasjawa · Post by **vikasjawa** » Thu Oct 25, 2007 3:35 am

Hi,
Other way of doing this can be as:
Read character by character of the string and then use this derivation to replace any other special character to space: if index('65|66|67|68|69|70|71|72|73|74|75|76|77|78|79|80|81|82|83|84|85|86|87|88|89|90|97|98|99|100|101|102|103|104|105|106|107|108|109|110|111|112|113|114|115|116|117|118|119|120|121|122|48|49|50|51|52|53|54|55|56|56|57|46|38|47|45|32' ,InputSingleCharacter,1) >=1then InputSingleCharacter else 32

uppalapati2003 · Post by **uppalapati2003** » Thu Oct 25, 2007 5:18 am

Hi i am getting 1000000 rec perday which i need to check one varchar2 (255) column this column contains average of 30 char " srini#vas is a go@od b|ye " how to work on this .