string function

Question

string function

swapna.katare

SSC Enthusiast

Points: 171
More actions
December 18, 2008 at 9:51 pm

#383608

Hi all,
I have a table Indus with 3 column serial,qh11,qh21
create table Indus(serial int,qh11 varchar(255),qh21 varchar(255))
serial qh11 qh21
1 234567890 45890
2 134567890 1345
3 2345679 49
4 23 35
5 234567890 2345
6 123456780 8
7 6
8 234567890 3458
My output should look like the below
serial qh11 qh21
4 23 35
7 6
In serial no 4 ,col qh21 has the number 3 and 5 and 5 is not present in qh11.Similarly serial no 7 qh21 has the number 6 which is not present in qh11. I want to check each number in qh21 must be present in qh11.
serial qh11 qh21
1 234567890 45890
2 134567890 1345
3 2345679 49
5 234567890 2345
6 123456780 8
8 234567890 3458
In serial no 1, qh11 has the value 234567890 and qh21 has 45890 . 4 is present in qh11,5 is present in qh11,8 is present in qh11,9 is present in qh11 and 0 is present in qh11.each number in qh21 is present in qh11. similarly with serial no 2,3,5,6 and 8,but is not the same in serial no 4 and 7.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Mike_D Hall of Fame Points: 3295 More actions · Answer 1

Here's a solution, I think. Functions aren't the most efficient things around so if your table is large performance may suffer. Just create the function below and then run your select statement as shown at the end of this post.

CREATE FUNCTION dbo.AllCharsInString

(

@vcQH11varchar(255),

@vcQH21varchar(255)

)

RETURNS int

AS

BEGIN

DECLARE@iQH21Lenint,

@iNextCharint,

@iMatchedbit

SET @iMatched = 1

SET @iQH21Len = LEN(@vcQH21)

SET @iNextChar = 1

WHILE @iNextChar <= @iQH21Len

AND @iMatched <> 0

BEGIN

SET @iMatched = CHARINDEX(SUBSTRING(@vcQH21,@iNextChar,1),@vcQH11)

SET @iNextChar = @iNextChar + 1

END

RETURN @iMatched

END

GO

Then you can run

SELECT serial,qh11,qh21 from Indus WHERE dbo.AllCharsInString (qh11,qh21) = 0

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Here's a tally-table solution which will run fast.

[font="Courier New"]DROP TABLE #Indus

CREATE TABLE #Indus (serial INT, qh11 VARCHAR(255), qh21 VARCHAR(255))

INSERT INTO #Indus (serial, qh11, qh21)

SELECT 1, '234567890', '45890' UNION ALL

SELECT 2, '134567890', '1345' UNION ALL

SELECT 3, '2345679', '49' UNION ALL

SELECT 4, '23', '35' UNION ALL

SELECT 5, '234567890', '2345' UNION ALL

SELECT 6, '123456780', '8' UNION ALL

SELECT 7,'', '6' UNION ALL

SELECT 8, '234567890', '3458'

SELECT i.serial, i.qh11, i.qh21

FROM #Indus i

INNER JOIN Numbers n ON n.number <= LEN(i.qh21)

WHERE CHARINDEX(SUBSTRING(i.qh21, n.number, 1), i.qh11) = 0

GROUP BY i.serial, i.qh11, i.qh21

[/font]

Output:

serial qh11 qh21

------ ---------- ----

4 23 35

7 6

Click here[/url] for hints and tips about construction and use of a tally table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

swapna.katare SSC Enthusiast Points: 171 More actions · Answer 3

thx but the query did not work. it is showing the error Invalid object name 'numbers'.what if i have 500 rows how to insert the values

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

swapna.katare (12/19/2008)
thx but the query did not work. it is showing the error Invalid object name 'numbers'.what if i have 500 rows how to insert the values

Click on the highlighted word 'here' in my previous post. It's a link. It will tell you what the invalid object 'numbers' is.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mike_D Hall of Fame Points: 3295 More actions · Answer 5

Just to help you out for your specific case:

create table Numbers (Number int)

go

insertintoNumbers values (1)

insertintoNumbers values (2)

insertintoNumbers values (3)

insertintoNumbers values (4)

insertintoNumbers values (5)

insertintoNumbers values (6)

insertintoNumbers values (7)

insertintoNumbers values (8)

insertintoNumbers values (9)

go

Don't insert a value for Number = 0 otherwise the code won't work.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

CREATE PROCEDURE [dbo].[MakeNumbersTable]

-- Courtesy of Jeff Moden, SSC

AS

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE dbo.Numbers

--===== Create and populate the Tally table

SELECT TOP 1000000

IDENTITY(int,1,1) AS number

INTO dbo.Numbers

FROM master.dbo.syscolumns sc1,

master.dbo.syscolumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Numbers

ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)

--===== Allow the general public to use it

GRANT SELECT ON dbo.Numbers TO PUBLIC

GO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

swapna.katare SSC Enthusiast Points: 171 More actions · Answer 7

this is the query which i have written.i m not getting the output

declare @a int

declare @b-2 int

set @a=1

while(@a<11)

begin

set @b-2=1

while(@b<11)

begin

select serial,qh11,qh21 from Indus

where substring(qh21,@a,1)=substring(qh11,@b,1)

or substring(qh21,@a,1)=''

set @b-2=@b+1

end

set @a=@a+1

end

Mike_D Hall of Fame Points: 3295 More actions · Answer 8

But that's not going to give you what you said you want. I'm debugging this in my head but if both strings have 10 digits then the "select" is going to be executed 100 times isn't it? If you follow Chris's solution you'll get the answer (mine works too but is likely to be a little slower). You could make a slight modification to the population of the Numbers table in that, since your two strings are 255 chars long, you only need rows from values 1 to 255. I think that's right.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

mdowns (12/19/2008)
But that's not going to give you what you said you want. I'm debugging this in my head but if both strings have 10 digits then the "select" is going to be executed 100 times isn't it? If you follow Chris's solution you'll get the answer (mine works too but is likely to be a little slower). You could make a slight modification to the population of the Numbers table in that, since your two strings are 255 chars long, you only need rows from values 1 to 255. I think that's right.

The solution I posted accounts for that, too!

INNER JOIN Numbers n ON n.number <= LEN(i.qh21)

It works and it's fast...but I'm bored now.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mike_D Hall of Fame Points: 3295 More actions · Answer 10

What I was saying was that

SELECT TOP 1000000

IDENTITY(int,1,1) AS number

INTO dbo.Numbers

FROM master.dbo.syscolumns sc1,

master.dbo.syscolumns sc2

could be replaced with

SELECT TOP 255

IDENTITY(int,1,1) AS number

INTO dbo.Numbers

FROM master.dbo.syscolumns sc1,

master.dbo.syscolumns sc2

Have to agree with you...I'm bored now

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 11

A solution without use of auxiliary table such as a tally table is this

;WITH Yak (Serial, qh21, c, n)

AS (

SELECTSerial,

qh21,

SUBSTRING(qh21, 1, 1),

1

FROMIndus

UNION ALL

SELECTSerial,

qh21,

SUBSTRING(qh21, n + 1, 1),

n + 1

FROMYak

WHEREn < DATALENGTH(qh21)

)

SELECTs.*

FROMIndus AS s

WHEREEXISTS (SELECT * FROM Yak AS y WHERE y.Serial = s.Serial AND s.qh11 NOT LIKE '%' + y.c + '%')

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 12

A solution for normalized tables would be

DECLARE@qh11 TABLE

(

Serial INT,

qh11 VARCHAR(1)

)

INSERT@qh11

SELECT1, '2' UNION ALL

SELECT1, '3' UNION ALL

SELECT1, '4' UNION ALL

SELECT1, '5' UNION ALL

SELECT1, '6' UNION ALL

SELECT1, '7' UNION ALL

SELECT1, '8' UNION ALL

SELECT1, '9' UNION ALL

SELECT1, '0' UNION ALL

SELECT2, '1' UNION ALL

SELECT2, '3' UNION ALL

SELECT2, '4' UNION ALL

SELECT2, '5' UNION ALL

SELECT2, '6' UNION ALL

SELECT2, '7' UNION ALL

SELECT2, '8' UNION ALL

SELECT2, '9' UNION ALL

SELECT2, '0' UNION ALL

SELECT3, '2' UNION ALL

SELECT3, '3' UNION ALL

SELECT3, '4' UNION ALL

SELECT3, '5' UNION ALL

SELECT3, '6' UNION ALL

SELECT3, '7' UNION ALL

SELECT3, '9' UNION ALL

SELECT4, '2' UNION ALL

SELECT4, '3' UNION ALL

SELECT5, '2' UNION ALL

SELECT5, '3' UNION ALL

SELECT5, '4' UNION ALL

SELECT5, '5' UNION ALL

SELECT5, '6' UNION ALL

SELECT5, '7' UNION ALL

SELECT5, '8' UNION ALL

SELECT5, '9' UNION ALL

SELECT5, '0' UNION ALL

SELECT6, '1' UNION ALL

SELECT6, '2' UNION ALL

SELECT6, '3' UNION ALL

SELECT6, '4' UNION ALL

SELECT6, '5' UNION ALL

SELECT6, '6' UNION ALL

SELECT6, '7' UNION ALL

SELECT6, '8' UNION ALL

SELECT6, '0' UNION ALL

SELECT7, '' UNION ALL

SELECT8, '2' UNION ALL

SELECT8, '3' UNION ALL

SELECT8, '4' UNION ALL

SELECT8, '5' UNION ALL

SELECT8, '6' UNION ALL

SELECT8, '7' UNION ALL

SELECT8, '8' UNION ALL

SELECT8, '9' UNION ALL

SELECT8, '0'

DECLARE@qh21 TABLE

(

Serial INT,

qh21 VARCHAR(1)

)

INSERT@qh21

SELECT1, '4' UNION ALL

SELECT1, '5' UNION ALL

SELECT1, '8' UNION ALL

SELECT1, '9' UNION ALL

SELECT1, '0' UNION ALL

SELECT2, '1' UNION ALL

SELECT2, '3' UNION ALL

SELECT2, '4' UNION ALL

SELECT2, '5' UNION ALL

SELECT3, '4' UNION ALL

SELECT3, '9' UNION ALL

SELECT4, '3' UNION ALL

SELECT4, '5' UNION ALL

SELECT5, '2' UNION ALL

SELECT5, '3' UNION ALL

SELECT5, '4' UNION ALL

SELECT5, '5' UNION ALL

SELECT6, '8' UNION ALL

SELECT7, '6' UNION ALL

SELECT8, '3' UNION ALL

SELECT8, '4' UNION ALL

SELECT8, '5' UNION ALL

SELECT8, '8'

SELECTt11.Serial

FROM@qh11 AS t11

INNER JOIN@qh21 AS t21 ON t21.Serial = t11.Serial

GROUP BYt11.Serial

HAVINGSUM(CASE WHEN t11.qh11 = t21.qh21 THEN 1 ELSE 0 END) < COUNT(DISTINCT t21.qh21)

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