query help in matching - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

query help in matching

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-14 : 09:15:20

I have the following update query:
update c set flagged=1 where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

how can i change this to also update a field with the actual 10 digit match

so if the match was 1234567897

if would do

update c set flagged=1,number='1234567897' where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 03:02:03

It should work. What happened when you tried?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 03:20:35

i don't know how to try

do i do

update c set flagged=1,number='%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 04:48:34

this doesn't work

select sbody,'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' from c where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

how do i pull out the what the 10 digit number is

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 04:49:05

Did you mean this?

update c set flagged=1,number=sbody where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 05:02:43

no i want the number to be = to just the 10 digit number that was found as a match

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 05:05:38

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 05:19:05

meaning sbody would be
'xyd dafsd kjk dfajsdk; her telephone is 2569853625 and her address is dfakdsf asd'

I want to extract the 10 digit number to a separate field when i find it in the text field.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 05:22:23

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 05:34:14

but I want only the match to the 10 digit number
if there is an address 123 my street - I don't want it to find the 123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 06:06:33

update c set flagged=1,number= substring(sbody ,patindex('%[0-9]%',sbody ),10) where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 06:21:47

thanks but how do i get just a 10 digit number match

this is returning the follwing as 10 digit numbers which is not correct

3C//DTD HT
3C//DTD HT
0px; padd

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 06:29:40

quote:
Originally posted by esthera

thanks but how do i get just a 10 digit number match

this is returning the follwing as 10 digit numbers which is not correct

3C//DTD HT
3C//DTD HT
0px; padd

How is it possible?
See what you get here

select substring(data,patindex('%[0-9]%',data),10) from
(
select 'xyd dafsd kjk dfajsdk; her telephone is 2569853625 and her address is dfakdsf asd' as data
union all
select 'huadf'
union all
select ' iuasduiyh 3C//DTD HT kuashd'
union all
select 'iuqweuh jh sdfjkjhn 0px; padd uuioqweouij oi'
) as t
where data like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 06:31:49

do this and you will see the problem - it pulls out the first but not matched properly (first is ok if it's really only 10 digits.)

select substring(data,patindex('%[0-9]%',data),10) from
(
select 'xyd dafsd kjk d3C//DTD HT kufajsdk; her telephone is 2569853625 and her address is dfakdsf asd' as data
union all
select 'huadf'
union all
select ' iuasduiyh 3C//DTD HT kuashd'
union all
select 'iuqweuh jh sdfjkjhn 0px; padd uuioqweouij oi'
) as t
where data like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 06:38:23

I really could not understand the requirement not even by sample data

Vaibhav T

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 06:51:37

I have a sbody of text and I want to pull out the 10 digit phone number of it.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 07:01:52

Is there any format of phone number or any where if you get 10 digits combinely then you want to pull out.
for ex -
if text is
'my name is vaibhav tiwari and i live in india on the address 30 - Central Road, MIDC Mumbai and my phone number is 9876543210'

so you want pull out 9876543210

but if +91 is prefix then what you want output.

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 07:14:53

One option is to use split function


CREATE FUNCTION dbo.extract_number (@s varchar(1000))
RETURNS bigint
AS
begin
	declare @n bigint;
		Declare @Text Varchar(100),@delimiter char(1)
		Set @Text = @s
		set @delimiter = ' '
		Declare @textXml Xml
		Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
		SELECT @n=data FROM
		(
		SELECT T.split.value('.', 'nvarchar(max)') AS data
		FROM @textXML.nodes('/d') T (split)
		) as t
		WHERE data like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  return (@n)
end
GO

update c set flagged=1,number= dbo.extract_number(sbody) where sbody like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' and dateadded>getdate()-1

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 07:25:18

thanks
i changed it to a select statement just to see but i get

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 14, end tag does not match start tag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 08:34:39

You may need to increase the size of @Text used in the function

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-15 : 08:41:04

changed it to nvarchar(max) but still the error

USE [traffica]
GO
/****** Object: UserDefinedFunction [dbo].[extract_number] Script Date: 03/15/2010 12:38:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[extract_number] (@s nvarchar(max))
RETURNS bigint
AS
begin
declare @n bigint;
Declare @Text nVarchar(max),@delimiter char(1)
Set @Text = @s
set @delimiter = ' '
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
SELECT @n=data FROM
(
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
) as t
WHERE data like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
return (@n)
end

error: Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 14, end tag does not match start tag

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources