| Author |
Topic |
|
johnsql
Posting Yak Master
161 Posts |
 Posted - 2007-11-09 : 12:25:48
|
| In a varchar(50)-typed column col1 of a table there are some blanks that can be located in any position in that column. Also, all kinds of readable characters of keyboard can take place in the column contents. How can we to recognize ("see") such whitespaces(e.g. blank, etc.) that are mixed with other readable in column col1?Thanks,johnsql |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-09 : 12:29:36
|
| try viewing your data in text mode in QA_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-11-09 : 12:32:50
|
| To be clear, Blanks can be seen but what I'd like that I can count how many blanks between non-blank chacters. For example, if col1=' 23 9~[] #%% ', then I can see there are blanks between substrings "23" and "9~[]", but I can NOT count how many there are such blanks between those 2 substrings. Any idea? |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-11-09 : 12:34:05
|
quote:
Originally posted by spirit1
try viewing your data in text mode in QA_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
Spirit, Thanks for your post, however, To be clear,Blanks can be seen but what I'd like that I can count how many blanks between non-blank chacters. For example, if col1=' 23 9~[] #%% ', then I can see there are blanks between substrings "23" and "9~[]", but I can NOT count how many there are such blanks between those 2 substrings.Any idea? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-09 : 12:37:56
|
| oh...then do:select len(YourColum) - len(replace(YourColum, ' ', ''))from yourTable_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-11-09 : 13:33:18
|
quote:
Originally posted by spirit1
oh...then do:select len(YourColum) - len(replace(YourColum, ' ', ''))from yourTable_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
What if blanks appear at the beginning or end of col1? It seems, in those scenarios, replace() functions do not work properly. Do you have any idea?Thank you in advance, |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-11-09 : 13:37:36
|
quote:
Originally posted by johnsql
quote:
Originally posted by spirit1
oh...then do:select len(YourColum) - len(replace(YourColum, ' ', ''))from yourTable_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
What if blanks appear at the beginning or end of col1? It seems, in those scenarios, replace() functions do not work properly. Do you have any idea?Thank you in advance,
I think in such scenarios, LEN() function excludes counting trailing blanks. So, len(YourColum) does not properly. It counts less, so len(YourColum) - len(replace(YourColum, ' ', '')) returns smaller results. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 13:43:24
|
Use DATALENGTH instead.Also, it might not be ASCII32 (space) as the spacing character. Look for ASCI160 too.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-09 : 13:43:25
|
| well then use datalength instead of len_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-09 : 13:44:34
|
1 second 
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-09 : 13:45:42
|
ouch!! that's the closest one yet  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-11-09 : 14:04:45
|
| Thank you all of you. DATALENGTH() works perfect. |
|
|
|
|
How able to see and whitespace characters