Copy first two characters of a string

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)

Copy first two characters of a string

Author

Topic

hickoire
Starting Member

5 Posts

Posted - 2005-09-26 : 15:53:12

Hi,

A simple question, but I don't know the full answer, or the best way to solve it.

Within my SQL database I have a table that has information about a person. Name address etc etc this is called tblcontact.

One field within tblcontact is the contacts UK Post Code (field is called 'Postcode').

What I need to do is create a trigger or stored procedure so that each time someone enters a new postcode or changes an existing post code the first text 2 or 3 text characters are copied to another field within the same table. This field is called 'mapcode'.

The problem I have is that some UK post codes start with two letters and then a number and some start one letter and then a number.

i.e.
E9 (I just want the E copied into a mapcode field from the postcode field)
GL54 (I just wna the GL copied into the mapcode field from the postcode field)

How can I copy just the first text characters before the first integer into another cell?

I fancy trigger.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-26 : 20:11:01

Without the CREATE TRIGGER syntax (and assuming that the codes will never start with a numeric):

DECLARE @code1 varchar(10), @code2 varchar(10)

SET @code1 = 'E9'
SET @code2 = 'GL54'

SELECT LEFT(@code1, PATINDEX('%[0-9]%', @code1) -1)
SELECT LEFT(@code2, PATINDEX('%[0-9]%', @code2) -1)

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-26 : 20:13:43

There are string manipulation funcitons in SQL...

SUBSTRING(UK_Post_Code_Column, 1, PATINDEX('%[0-9]%', UK_Post_Code_Column)-1)

The above will fail if the data is missing a numeric. So you should be careful and use a CASE statement to check for this condition:
[CODE]
CASE WHEN PATINDEX('%[0-9]%', UK_Post_Code_Column) > 0
THEN SUBSTRING(UK_Post_Code_Column, 1, PATINDEX('%[0-9]%', UK_Post_Code_Column)-1)
ELSE 'ERROR'
END[/CODE]

Edit: I was sniped

Kristen
Test

22859 Posts

Posted - 2005-09-26 : 21:24:45

You might want to allow for a space, as well as a digit, as the end-marker - I dunno if its still used, but there used to be a "GIR xxx" postcode for the Giro.

i.e.


PATINDEX('%[0-9 ]%', @code1) 
               ^

Kristen

hickoire
Starting Member

5 Posts

Posted - 2005-09-27 : 03:57:50

thanks for the responses.

I have tried to include the Case statement into a trigger but I have had no luck can anyone help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 04:07:27

Post the Trigger code you used

Madhivanan

Failing to plan is Planning to fail

hickoire
Starting Member

5 Posts

Posted - 2005-09-27 : 08:41:45

His is my trigger. Only new so I assume it is a shocker!

CREATE TRIGGER PostcodeUpdate
ON tblContact
FOR Update
AS
declare @a int
IF update(Date_Updated)
BEGIN
set @a = 1 --Does nothing actually
END
IF update (Competitor)
BEGIN
CASE WHEN PATINDEX('%[0-9]%', UK_Post_Code_Column) > 0
THEN SUBSTRING(UK_Post_Code_Column, 1, PATINDEX('%[0-9]%', UK_Post_Code_Column)-1)
ELSE 'ERROR'
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 08:51:30

I think you miss update part

Update tblContact Set col=
CASE WHEN PATINDEX('%[0-9]%', UK_Post_Code_Column) > 0
THEN SUBSTRING(UK_Post_Code_Column, 1, PATINDEX('%[0-9]%', UK_Post_Code_Column)-1) END

Madhivanan

Failing to plan is Planning to fail

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-27 : 09:53:40

[code]
CREATE TRIGGER PostcodeUpdate
ON tblContact
FOR Update
AS
declare @a int
IF update(Date_Updated)
BEGIN
set @a = 1 --Does nothing actually
END
IF update (Competitor)
BEGIN
Update tblContact
Set MAPCODE = CASE WHEN PATINDEX('%[0-9]%', UK_Post_Code_Column) > 0
THEN SUBSTRING(UK_Post_Code_Column, 1, PATINDEX('%[0-9]%', UK_Post_Code_Column)-1)
ELSE 'ER' -- Or return whatever 2-characters you feel are best
END
END
[/code]

hickoire
Starting Member

5 Posts

Posted - 2005-09-27 : 10:10:16

This works!

Thanks for your help!

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources