| Author |
 Topic  |
|
|
Mathias
Posting Yak Master
France
114 Posts |
 Posted - 03/02/2004 : 03:30:29
|
I need to create a table to enter record various strings that should be converted to a new value. One of the change that I need to record is: change space to null.
create TABLE [TEXT_CONVERSION] (
[REC_NUM] [int] IDENTITY (1, 1) NOT NULL ,
[SOURCE_STR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONVERTED_STR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
I don't manage to record the space in the varchar field.
Thanks, Paul |
|
|
rihardh
Constraint Violating Yak Guru
Slovenia
307 Posts |
Posted - 03/02/2004 : 03:54:46
|
Change space to null? I don't get it.
More info please... |
 |
|
|
Mathias
Posting Yak Master
France
114 Posts |
Posted - 03/02/2004 : 04:09:31
|
I want to replace in the string 'John Smith', I need to replace the space with nothing to get 'JohnSmith. The value char(32) needs to be replace by nothing. I am trying to get the char(32) and its replaced by value in the table but it doesn't work.
|
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1384 Posts |
Posted - 03/02/2004 : 04:26:13
|
select REPLACE('John Smith', ' ', '')
Duane. |
|
|
|
Mathias
Posting Yak Master
France
114 Posts |
Posted - 03/02/2004 : 04:40:04
|
I have other replacements to do, I know that the replace function will be used.
I need to replace
1-char(32) with ''
2-'Ltd' with 'Limited'
3-'Ltd ' with 'Limited'
and so on. The changes are kept in the above table.
When I insert ' ', '' in the table, the two fields contain ''. So later on, when I use a cursor to do all the successive replacements, it does all of them excepted the one finishing by space. The space character is trimmed and the changes 1 and 3 are not done.
Paul |
|
|
|
ChrisFretwell
Starting Member
Canada
43 Posts |
Posted - 03/02/2004 : 11:30:43
|
You can nest the replace statements. Or write a user defined function with a bunch of if then replace's or the nested replace.
For the nesting you can just replace(replace(fielda,' ',''),'Ltd','Limited') etc.
If you have the replace on the blank space first, then you dont need to check for both 'ltd' and 'ltd '.
If you think you will need to do this for more than one field, or in more than one place then put it in a function, if its limited used, then ues the nested replace.
chris |
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3245 Posts |
Posted - 03/04/2004 : 14:14:37
|
If you're trying to record what special character conversions are being done, instead of listing just the textual representation, you might use the ASCII number (i.e. Space = 32).
These two are equivalent
select replace('John Smith', ' ', '')
select replace('John Smith', char(32), '')
Not sure what you'd use for the second part because doing char(0) doesn't return the right result.
--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc |
|
|
|
Lumbago
Norsk Yak Master
Norway
2079 Posts |
Posted - 03/04/2004 : 15:00:22
|
If you have alot of these replacements I think I would have made a user defined function for it.
CREATE FUNCTION dbo.MyReplace (@inText varchar(100))
RETURNS varchar(100)
AS
BEGIN
SET @inText = (SELECT REPLACE(@inText, ' ', ''))
SET @inText = (SELECT REPLACE(@inText, 'Ltd', 'Limited'))
RETURN @inText
END
and then do sort of like this:
INSERT INTO table ...
VALUES (dbo.MyReplace('John Smith Ltd'), ...)
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
| |
Topic |
|
How avoiding Varchar field triming space character
