| Author |
Topic |
|
Mathias
Posting Yak Master
119 Posts |
 Posted - 2004-03-02 : 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
307 Posts |
Posted - 2004-03-02 : 03:54:46
|
| Change space to null? I don't get it.More info please... |
 |
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2004-03-02 : 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
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-03-02 : 04:26:13
|
| select REPLACE('John Smith', ' ', '')Duane. |
|
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2004-03-02 : 04:40:04
|
| I have other replacements to do, I know that the replace function will be used. I need to replace1-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
43 Posts |
Posted - 2004-03-02 : 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
3246 Posts |
Posted - 2004-03-04 : 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 equivalentselect 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 [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-03-04 : 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)ASBEGIN SET @inText = (SELECT REPLACE(@inText, ' ', '')) SET @inText = (SELECT REPLACE(@inText, 'Ltd', 'Limited')) RETURN @inTextENDand 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" |
|
|
|
|
How avoiding Varchar field triming space character