ignoring dot and space in a value

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)

ignoring dot and space in a value

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 13:06:53

I am using SQL 2005 and I need to filter out certain part in a value like extension or space .

Values for the below columns SecurityID and security_id are NL0000395903.GR and NL0000395903 GR.

If I use the below I still get the dot at the end like NL0000395903. but I need to match on NL0000395903 and ignore .GR or GR

UPDATE #tempXRaw
SET SecurityID = LEFT(e.security_id, CharIndex('.',e.security_id))

Thanks in advance for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 13:14:07

You can use the REPLACE function to replace characters with ''. But you can just add a -1 to your LEFT function to get rid of that last character.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 13:14:10

UPDATE #tempXRaw
SET SecurityID = PARSENAME(REPLACE(security_id,' ','.'),2)

This will work if you have a ' ' or '.' always coming in your value.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 13:59:03

But the value is not always a ' ' or a '.'. some values can be plain without the extension or space like US9108734057

So when I try to match up the value, I need to indicate to ignore those that have the '.' or ' ' extensions so that these values appear in the final output.

Can I still use the above?

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 14:41:21

Is there a way to match up those values that do not have a dot extension or a space as well ignore those that have a dot extension and the space?

Now I am not seeing the plain values like US9108734057

I guess because we are assuming all the values are of type NL0000395903.GR and NL0000395903 GR

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:07:25

Please post an example of each of the data points that you'll need to "fix".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 15:19:32

Hi, these are the data type we will be receiving :

SE0000818569.NO
GB00B15PVN63
NL0000395903 GR

We need to match on SE0000818569, GB00B15PVN63 and NL0000395903 ignoring the .NO and GR(space) that is anything that is after the dot or space so we get an exact match.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:23:13

Will you always want just the first 12 characters? If so just use the LEFT function: LEFT(Column1, 12)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 15:32:17

No. some values are B01XYM7 GB or NULL or 55262C100 so I cannot assume the length is always 12.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:39:06

See visakh's solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 16:07:34

I did and I din't like what I saw.

DECLARE	@Sample TABLE (Info VARCHAR(100))

INSERT	@Sample
SELECT	'SE0000818569.NO' UNION ALL
SELECT	'GB00B15PVN63' UNION ALL
SELECT	'NL0000395903 GR' UNION ALL
SELECT	'B01XYM7 GB' UNION ALL
SELECT	NULL UNION ALL
SELECT	'55262C100'

SELECT	Info AS Original,
	PARSENAME(REPLACE(Info,' ', '.'), 2) AS Visakh16,
	LEFT(Info, COALESCE(NULLIF(PATINDEX('%[^0-9a-z]%', Info), 0) - 1, LEN(Info))) AS Peso1,
	LEFT(Info, COALESCE(NULLIF(PATINDEX('%[ .]%', Info), 0) - 1, LEN(Info))) AS Peso2,
	COALESCE(PARSENAME(REPLACE(Info, ' ', '.'), 2), PARSENAME(REPLACE(Info, ' ', '.'), 1)) AS AlteredVisakh16
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-21 : 17:26:11

Thanks Peso, I tried this test and it works but when I use the following within the code, it gives this error. ERROR - Server: Msg 8116, Level 16, State 1, Procedure SMRebuildRestrictedSecurities, Line 155
Argument data type int is invalid for argument 2 of patindex function.

UPDATE #tempXRaw
SET SecurityID = LEFT(e.security_id, COALESCE(NULLIF(PATINDEX('%[ .]%', e.security_id), NULL) - 1, LEN(e.security_id))) --e.security_id
FROM #tempXRaw xRaw

select COALESCE(PARSENAME(REPLACE('SE0000818569.NO', ' ', '.'), 2), PARSENAME(REPLACE('SE0000818569.NO', ' ', '.'), 1))
select COALESCE(PARSENAME(REPLACE('55262C100', ' ', '.'), 2), PARSENAME(REPLACE('55262C100', ' ', '.'), 1))
select LEFT('SE0000818569.NO', COALESCE(NULLIF(PATINDEX('%[ .]%', 'SE0000818569.NO'), 0) - 1, LEN('SE0000818569.NO')))
select LEFT('55262C100', COALESCE(NULLIF(PATINDEX('%[ .]%', '55262C100'), 0) - 1, LEN('55262C100')))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 17:30:28

Why did you change ", 0" to ", NULL" ???

E 12°55'05.25"
N 56°04'39.16"

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-22 : 09:13:32

I used ",0" first and got the same error. Then I changed it to ", NULL" thinking that would resolve the error but I got the same error

I used this one originally:

LEFT(e.security_id, COALESCE(NULLIF(PATINDEX('%[ .]%', e.security_id), 0) - 1, LEN(e.security_id)))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 09:20:33

As the error says, you are inputting the wrong colum name.
You are updating the "SecurityID" column but are sending column "security_id" to the patindex function.

E 12°55'05.25"
N 56°04'39.16"

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-22 : 13:49:15

Thanks.

I used the below and it works for "." but not space.

I am able to ignore ".NO" in SE0000818569.NO but I am not able to find " GB" in B0W48T4 GB. So the below is probably not finding space. Thanks in advance for your help.

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))--e.external_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 13:56:36

quote:
Originally posted by sqlnovice123

Thanks.

I used the below and it works for "." but not space.

I am able to ignore ".NO" in SE0000818569.NO but I am not able to find " GB" in B0W48T4 GB. So the below is probably not finding space. Thanks in advance for your help.

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))--e.external_id

why are you matching values to first 6 characters of Sedol field? Your sample data shows that you've 12 characters coming in some cases so how can you match b/w them?

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-05-22 : 15:10:49

The sample data can be 12 characters or even lesser but for the below the existing code matches on the first 6 and 8 characters are matched for SEDOL and CUSIP because our database objects store them like that, for ISINS and rest there is no restriction on the length of the value.

Can I do something like this for the SEDOL? Similarly for the CUSIP use LEFT(e.external_id,8) ?

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(LEFT(e.external_id,6), ' ', '.'), 2), PARSENAME(REPLACE(LEFT(e.external_id,6), ' ', '.'), 1))

sample input values:

Query 1
UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.Sedol, 6) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))

Query 2

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON LEFT(xRaw.ExternalID, 8) = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'),
, PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))

Query 3

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON xRaw.ExternalID = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))--e.external_id

Query 4

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON xRaw.ExternalID = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))--e.external_id

Query 5

UPDATE #tempXRaw
SET SecurityID = e.security_id
FROM #tempXRaw xRaw

JOIN intldb..external_id_snapshot_table e
ON xRaw.ExternalID = COALESCE(PARSENAME(REPLACE(e.external_id, ' ', '.'), 2), PARSENAME(REPLACE(e.external_id, ' ', '.'), 1))--e.external_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 17:21:19

Use Peso2 approach.

Do you really want to handle funds (SEDOL is identity for funds) with faulty code?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources