use substring to populate a field in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

use substring to populate a field in a table

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-21 : 11:44:43

ID Field1 Field2
1 NULL ASDFASdf234234aa111 asdfsadf234234asdf
2 NULL
3 NULL ASDFASDFSADF234234aa2 ASDFASDFlwwl333
4 NULL
5 NULL ASDFSdflwerwejkqr12321

Problem:

If Field2 is not empty, I want to take the first set of characters and assign them to Field1.

So field#1 should look like:

Field1
ASDFASdf234234aa111

ASDFASDFSADF234234aa2

ASDFSdflwerwejkqr12321

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-21 : 11:53:00

Use CHARINDEX() to find the space - use the position returned by CHARINDEX() minus 1 as the length parameter to your SUBSTRING()

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-21 : 11:54:44

That last row was a bit tricky :)


DECLARE @MyTable table (ID INT, Field1 VARCHAR(255), Field2 VARCHAR(255))

INSERT INTO @MyTable(ID, Field1, Field2) VALUES(1, NULL, 'ASDFASdf234234aa111 asdfsadf234234asdf')
INSERT INTO @MyTable(ID, Field1, Field2) VALUES(2, NULL, '')
INSERT INTO @MyTable(ID, Field1, Field2) VALUES(3, NULL, 'ASDFASDFSADF234234aa2 ASDFASDFlwwl333')
INSERT INTO @MyTable(ID, Field1, Field2) VALUES(4, NULL, '')
INSERT INTO @MyTable(ID, Field1, Field2) VALUES(5, NULL, 'ASDFSdflwerwejkqr12321')

UPDATE @MyTable SET
Field1 = SUBSTRING(Field2, 1, CASE WHEN PATINDEX('% %', Field2) = 0 then LEN(Field2) ELSE PATINDEX('% %', Field2) END)
FROM @MyTable
WHERE Field2 <> ''

select * from @MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-21 : 11:55:28

CharIndex That's what i was looking for!
I'd use that in place of PatIndex.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-21 : 12:23:06

thanks for the prompt help!
i love this forum!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources