Phone number challenge - 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

Site Related Forums

The Yak Corral

Phone number challenge

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-08 : 06:08:29

I came across this challenge:
Programming Challenge: Phraser

Any takers for an SQL solution ?
Is SQL an appropriate tool for solving this kind of problem ?

An HASKELL solution is found here

rockmoose

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-08 : 20:24:48

Worth pointing out to him that his example "642-394-6369 can be phrased as nice-window" is erroneous, since "nice" is not in his English Word list....

PS - Working on a solution

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-09 : 04:25:56

Yes, I saw that mentioned in the comments of the blog:

>""Nice" isn't in the dictionary list that you provided. "
>"neither is "in", "win", or "fox". just to let everybody know for testing purposes. "
>"Your result list is bigger because your word list contains "ox", which mine doesn't have. "

Not near a solution yet...

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-09 : 09:05:48

Here's a rough first draft ... note that in the middle there are two parameters: the phone number (@Number) and also the maximum number of numeric digits in your results (@MaxNumericDigits); i.e., if you set @MaxNumericDigits equal to 2, then "12-this" would be allowed, but not "128-his". Setting it 0 means no numbers are allow. Without that being set to reasonable value (i.e., >=2 ) the results are pretty boring and useless. The results are cool when you set it to 1.

For testing and playing around, it is quickest to create the Words table first and insert the data, then comment out the add/insert/drop statements for that table in further runs.

step 1: Create the words table (run once only)


create table Words(Word varchar(100) primary key)

set nocount on

insert into Words (Word)
select 'a' union all
select 'able' union all
select 'about' union all
select 'account' union all
select 'acid' union all
select 'across' union all
select 'act' union all
select 'addition' union all
select 'adjustment' union all
select 'advertisement' union all
select 'after' union all
select 'again' union all
select 'against' union all
select 'agreement' union all
select 'air' union all
select 'all' union all
select 'almost' union all
select 'among' union all
select 'amount' union all
select 'amusement' union all
select 'and' union all
select 'angle' union all
select 'angry' union all
select 'animal' union all
select 'answer' union all
select 'ant' union all
select 'any' union all
select 'apparatus' union all
select 'apple' union all
select 'approval' union all
select 'arch' union all
select 'argument' union all
select 'arm' union all
select 'army' union all
select 'art' union all
select 'as' union all
select 'at' union all
select 'attack' union all
select 'attempt' union all
select 'attention' union all
select 'attraction' union all
select 'authority' union all
select 'automatic' union all
select 'awake' union all
select 'baby' union all
select 'back' union all
select 'bad' union all
select 'bag' union all
select 'balance' union all
select 'ball' union all
select 'band' union all
select 'base' union all
select 'basin' union all
select 'basket' union all
select 'bath' union all
select 'be' union all
select 'beautiful' union all
select 'because' union all
select 'bed' union all
select 'bee' union all
select 'before' union all
select 'behaviour' union all
select 'belief' union all
select 'bell' union all
select 'bent' union all
select 'berry' union all
select 'between' union all
select 'bird' union all
select 'birth' union all
select 'bit' union all
select 'bite' union all
select 'bitter' union all
select 'black' union all
select 'blade' union all
select 'blood' union all
select 'blow' union all
select 'blue' union all
select 'board' union all
select 'boat' union all
select 'body' union all
select 'boiling' union all
select 'bone' union all
select 'book' union all
select 'boot' union all
select 'bottle' union all
select 'box' union all
select 'boy' union all
select 'brain' union all
select 'brake' union all
select 'branch' union all
select 'brass' union all
select 'bread' union all
select 'breath' union all
select 'brick' union all
select 'bridge' union all
select 'bright' union all
select 'broken' union all
select 'brother' union all
select 'brown' union all
select 'brush' union all
select 'bucket' union all
select 'building' union all
select 'bulb' union all
select 'burn' union all
select 'burst' union all
select 'business' union all
select 'but' union all
select 'butter' union all
select 'button' union all
select 'by' union all
select 'cake' union all
select 'camera' union all
select 'canvas' union all
select 'card' union all
select 'care' union all
select 'carriage' union all
select 'cart' union all
select 'cat' union all
select 'cause' union all
select 'certain' union all
select 'chain' union all
select 'chalk' union all
select 'chance' union all
select 'change' union all
select 'cheap' union all
select 'cheese' union all
select 'chemical' union all
select 'chest' union all
select 'chief' union all
select 'chin' union all
select 'church' union all
select 'circle' union all
select 'clean' union all
select 'clear' union all
select 'clock' union all
select 'cloth' union all
select 'cloud' union all
select 'coal' union all
select 'coat' union all
select 'cold' union all
select 'collar' union all
select 'colour' union all
select 'comb' union all
select 'come' union all
select 'comfort' union all
select 'committee' union all
select 'common' union all
select 'company' union all
select 'comparison' union all
select 'competition' union all
select 'complete' union all
select 'complex' union all
select 'condition' union all
select 'connection' union all
select 'conscious' union all
select 'control' union all
select 'cook' union all
select 'copper' union all
select 'copy' union all
select 'cord' union all
select 'cork' union all
select 'cotton' union all
select 'cough' union all
select 'country' union all
select 'cover' union all
select 'cow' union all
select 'crack' union all
select 'credit' union all
select 'crime' union all
select 'cruel' union all
select 'crush' union all
select 'cry' union all
select 'cup' union all
select 'current' union all
select 'curtain' union all
select 'curve' union all
select 'cushion' union all
select 'damage' union all
select 'danger' union all
select 'dark' union all
select 'daughter' union all
select 'day' union all
select 'dead' union all
select 'dear' union all
select 'death' union all
select 'debt' union all
select 'decision' union all
select 'deep' union all
select 'degree' union all
select 'delicate' union all
select 'dependent' union all
select 'design' union all
select 'desire' union all
select 'destruction' union all
select 'detail' union all
select 'development' union all
select 'different' union all
select 'digestion' union all
select 'direction' union all
select 'dirty' union all
select 'discovery' union all
select 'discussion' union all
select 'disease' union all
select 'disgust' union all
select 'distance' union all
select 'distribution' union all
select 'division' union all
select 'do' union all
select 'dog' union all
select 'door' union all
select 'doubt' union all
select 'down' union all
select 'drain' union all
select 'drawer' union all
select 'dress' union all
select 'drink' union all
select 'driving' union all
select 'drop' union all
select 'dry' union all
select 'dust' union all
select 'ear' union all
select 'early' union all
select 'earth' union all
select 'east' union all
select 'edge' union all
select 'education' union all
select 'effect' union all
select 'egg' union all
select 'elastic' union all
select 'electric' union all
select 'end' union all
select 'engine' union all
select 'enough' union all
select 'equal' union all
select 'error' union all
select 'even' union all
select 'event' union all
select 'ever' union all
select 'every' union all
select 'example' union all
select 'exchange' union all
select 'existence' union all
select 'expansion' union all
select 'experience' union all
select 'expert' union all
select 'eye' union all
select 'face' union all
select 'fact' union all
select 'fall' union all
select 'FALSE' union all
select 'family' union all
select 'far' union all
select 'farm' union all
select 'fat' union all
select 'father' union all
select 'fear' union all
select 'feather' union all
select 'feeble' union all
select 'feeling' union all
select 'female' union all
select 'fertile' union all
select 'fiction' union all
select 'field' union all
select 'fight' union all
select 'finger' union all
select 'fire' union all
select 'first' union all
select 'fish' union all
select 'fixed' union all
select 'flag' union all
select 'flame' union all
select 'flat' union all
select 'flight' union all
select 'floor' union all
select 'flower' union all
select 'fly' union all
select 'fold' union all
select 'food' union all
select 'foolish' union all
select 'foot' union all
select 'for' union all
select 'force' union all
select 'fork' union all
select 'form' union all
select 'forward' union all
select 'fowl' union all
select 'frame' union all
select 'free' union all
select 'frequent' union all
select 'friend' union all
select 'from' union all
select 'front' union all
select 'fruit' union all
select 'full' union all
select 'future' union all
select 'garden' union all
select 'general' union all
select 'get' union all
select 'girl' union all
select 'give' union all
select 'glass' union all
select 'glove' union all
select 'go' union all
select 'goat' union all
select 'gold' union all
select 'good' union all
select 'government' union all
select 'grain' union all
select 'grass' union all
select 'great' union all
select 'green' union all
select 'grey' union all
select 'grip' union all
select 'group' union all
select 'growth' union all
select 'guide' union all
select 'gun' union all
select 'hair' union all
select 'hammer' union all
select 'hand' union all
select 'hanging' union all
select 'happy' union all
select 'harbour' union all
select 'hard' union all
select 'harmony' union all
select 'hat' union all
select 'hate' union all
select 'have' union all
select 'he' union all
select 'head' union all
select 'healthy' union all
select 'hear' union all
select 'hearing' union all
select 'heart' union all
select 'heat' union all
select 'help' union all
select 'high' union all
select 'history' union all
select 'hole' union all
select 'hollow' union all
select 'hook' union all
select 'hope' union all
select 'horn' union all
select 'horse' union all
select 'hospital' union all
select 'hour' union all
select 'house' union all
select 'how' union all
select 'humour' union all
select 'I' union all
select 'ice' union all
select 'idea' union all
select 'if' union all
select 'ill' union all
select 'important' union all
select 'impulse' union all
select 'in' union all
select 'increase' union all
select 'industry' union all
select 'ink' union all
select 'insect' union all
select 'instrument' union all
select 'insurance' union all
select 'interest' union all
select 'invention' union all
select 'iron' union all
select 'island' union all
select 'jelly' union all
select 'jewel' union all
select 'join' union all
select 'journey' union all
select 'judge' union all
select 'jump' union all
select 'keep' union all
select 'kettle' union all
select 'key' union all
select 'kick' union all
select 'kind' union all
select 'kiss' union all
select 'knee' union all
select 'knife' union all
select 'knot' union all
select 'knowledge' union all
select 'land' union all
select 'language' union all
select 'last' union all
select 'late' union all
select 'laugh' union all
select 'law' union all
select 'lead' union all
select 'leaf' union all
select 'learning' union all
select 'leather' union all
select 'left' union all
select 'leg' union all
select 'let' union all
select 'letter' union all
select 'level' union all
select 'library' union all
select 'lift' union all
select 'light' union all
select 'like' union all
select 'limit' union all
select 'line' union all
select 'linen' union all
select 'lip' union all
select 'liquid' union all
select 'list' union all
select 'little' union all
select 'living' union all
select 'lock' union all
select 'long' union all
select 'look' union all
select 'loose' union all
select 'loss' union all
select 'loud' union all
select 'love' union all
select 'low' union all
select 'machine' union all
select 'make' union all
select 'male' union all
select 'man' union all
select 'manager' union all
select 'map' union all
select 'mark' union all
select 'market' union all
select 'married' union all
select 'mass' union all
select 'match' union all
select 'material' union all
select 'may' union all
select 'meal' union all
select 'measure' union all
select 'meat' union all
select 'medical' union all
select 'meeting' union all
select 'memory' union all
select 'metal' union all
select 'middle' union all
select 'military' union all
select 'milk' union all
select 'mind' union all
select 'mine' union all
select 'minute' union all
select 'mist' union all
select 'mixed' union all
select 'money' union all
select 'monkey' union all
select 'month' union all
select 'moon' union all
select 'morning' union all
select 'mother' union all
select 'motion' union all
select 'mountain' union all
select 'mouth' union all
select 'move' union all
select 'much' union all
select 'muscle' union all
select 'music' union all
select 'nail' union all
select 'name' union all
select 'narrow' union all
select 'nation' union all
select 'natural' union all
select 'near' union all
select 'necessary' union all
select 'neck' union all
select 'need' union all
select 'needle' union all
select 'nerve' union all
select 'net' union all
select 'new' union all
select 'news' union all
select 'nice' union all
select 'night' union all
select 'no' union all
select 'noise' union all
select 'normal' union all
select 'north' union all
select 'nose' union all
select 'not' union all
select 'note' union all
select 'now' union all
select 'number' union all
select 'nut' union all
select 'observation' union all
select 'of' union all
select 'off' union all
select 'offer' union all
select 'office' union all
select 'oil' union all
select 'old' union all
select 'on' union all
select 'only' union all
select 'open' union all
select 'operation' union all
select 'opinion' union all
select 'opposite' union all
select 'or' union all
select 'orange' union all
select 'order' union all
select 'organization' union all
select 'ornament' union all
select 'other' union all
select 'out' union all
select 'oven' union all
select 'over' union all
select 'owner' union all
select 'page' union all
select 'pain' union all
select 'paint' union all
select 'paper' union all
select 'parallel' union all
select 'parcel' union all
select 'part' union all
select 'past' union all
select 'paste' union all
select 'payment' union all
select 'peace' union all
select 'pen' union all
select 'pencil' union all
select 'person' union all
select 'physical' union all
select 'picture' union all
select 'pig' union all
select 'pin' union all
select 'pipe' union all
select 'place' union all
select 'plane' union all
select 'plant' union all
select 'plate' union all
select 'play' union all
select 'please' union all
select 'pleasure' union all
select 'plough' union all
select 'pocket' union all
select 'point' union all
select 'poison' union all
select 'polish' union all
select 'political' union all
select 'poor' union all
select 'porter' union all
select 'position' union all
select 'possible' union all
select 'pot' union all
select 'potato' union all
select 'powder' union all
select 'power' union all
select 'present' union all
select 'price' union all
select 'print' union all
select 'prison' union all
select 'private' union all
select 'probable' union all
select 'process' union all
select 'produce' union all
select 'profit' union all
select 'property' union all
select 'prose' union all
select 'protest' union all
select 'public' union all
select 'pull' union all
select 'pump' union all
select 'punishment' union all
select 'purpose' union all
select 'push' union all
select 'put' union all
select 'quality' union all
select 'question' union all
select 'quick' union all
select 'quiet' union all
select 'quite' union all
select 'rail' union all
select 'rain' union all
select 'range' union all
select 'rat' union all
select 'rate' union all
select 'ray' union all
select 'reaction' union all
select 'reading' union all
select 'ready' union all
select 'reason' union all
select 'receipt' union all
select 'record' union all
select 'red' union all
select 'regret' union all
select 'regular' union all
select 'relation' union all
select 'religion' union all
select 'representative' union all
select 'request' union all
select 'respect' union all
select 'responsible' union all
select 'rest' union all
select 'reward' union all
select 'rhythm' union all
select 'rice' union all
select 'right' union all
select 'ring' union all
select 'river' union all
select 'road' union all
select 'rod' union all
select 'roll' union all
select 'roof' union all
select 'room' union all
select 'root' union all
select 'rough' union all
select 'round' union all
select 'rub' union all
select 'rule' union all
select 'run' union all
select 'sad' union all
select 'safe' union all
select 'sail' union all
select 'salt' union all
select 'same' union all
select 'sand' union all
select 'say' union all
select 'scale' union all
select 'school' union all
select 'science' union all
select 'scissors' union all
select 'screw' union all
select 'sea' union all
select 'seat' union all
select 'second' union all
select 'secret' union all
select 'secretary' union all
select 'see' union all
select 'seed' union all
select 'seem' union all
select 'selection' union all
select 'self' union all
select 'send' union all
select 'sense' union all
select 'separate' union all
select 'serious' union all
select 'servant' union all
select 'sex' union all
select 'shade' union all
select 'shake' union all
select 'shame' union all
select 'sharp' union all
select 'sheep' union all
select 'shelf' union all
select 'ship' union all
select 'shirt' union all
select 'shock' union all
select 'shoe' union all
select 'short' union all
select 'shut' union all
select 'side' union all
select 'sign' union all
select 'silk' union all
select 'silver' union all
select 'simple' union all
select 'sister' union all
select 'size' union all
select 'skin' union all
select 'skirt' union all
select 'sky' union all
select 'sleep' union all
select 'slip' union all
select 'slope' union all
select 'slow' union all
select 'small' union all
select 'smash' union all
select 'smell' union all
select 'smile' union all
select 'smoke' union all
select 'smooth' union all
select 'snake' union all
select 'sneeze' union all
select 'snow' union all
select 'so' union all
select 'soap' union all
select 'society' union all
select 'sock' union all
select 'soft' union all
select 'solid' union all
select 'some' union all
select 'son' union all
select 'song' union all
select 'sort' union all
select 'sound' union all
select 'soup' union all
select 'south' union all
select 'space' union all
select 'spade' union all
select 'special' union all
select 'sponge' union all
select 'spoon' union all
select 'spring' union all
select 'square' union all
select 'stage' union all
select 'stamp' union all
select 'star' union all
select 'start' union all
select 'statement' union all
select 'station' union all
select 'steam' union all
select 'steel' union all
select 'stem' union all
select 'step' union all
select 'stick' union all
select 'sticky' union all
select 'stiff' union all
select 'still' union all
select 'stitch' union all
select 'stocking' union all
select 'stomach' union all
select 'stone' union all
select 'stop' union all
select 'store' union all
select 'story' union all
select 'straight' union all
select 'strange' union all
select 'street' union all
select 'stretch' union all
select 'strong' union all
select 'structure' union all
select 'substance' union all
select 'such' union all
select 'sudden' union all
select 'sugar' union all
select 'suggestion' union all
select 'summer' union all
select 'sun' union all
select 'support' union all
select 'surprise' union all
select 'sweet' union all
select 'swim' union all
select 'system' union all
select 'table' union all
select 'tail' union all
select 'take' union all
select 'talk' union all
select 'tall' union all
select 'taste' union all
select 'tax' union all
select 'teaching' union all
select 'tendency' union all
select 'test' union all
select 'than' union all
select 'that' union all
select 'the' union all
select 'then' union all
select 'theory' union all
select 'there' union all
select 'thick' union all
select 'thin' union all
select 'thing' union all
select 'this' union all
select 'thought' union all
select 'thread' union all
select 'throat' union all
select 'through' union all
select 'thumb' union all
select 'thunder' union all
select 'ticket' union all
select 'tight' union all
select 'till' union all
select 'time' union all
select 'tin' union all
select 'tired' union all
select 'to' union all
select 'toe' union all
select 'together' union all
select 'tomorrow' union all
select 'tongue' union all
select 'tooth' union all
select 'top' union all
select 'touch' union all
select 'town' union all
select 'trade' union all
select 'train' union all
select 'transport' union all
select 'tray' union all
select 'tree' union all
select 'trick' union all
select 'trouble' union all
select 'trousers' union all
select 'TRUE' union all
select 'turn' union all
select 'twist' union all
select 'umbrella' union all
select 'under' union all
select 'unit' union all
select 'up' union all
select 'use' union all
select 'value' union all
select 'verse' union all
select 'very' union all
select 'vessel' union all
select 'view' union all
select 'violent' union all
select 'voice' union all
select 'waiting' union all
select 'walk' union all
select 'wall' union all
select 'war' union all
select 'warm' union all
select 'wash' union all
select 'waste' union all
select 'watch' union all
select 'water' union all
select 'wave' union all
select 'wax' union all
select 'way' union all
select 'weather' union all
select 'week' union all
select 'weight' union all
select 'well' union all
select 'west' union all
select 'wet' union all
select 'wheel' union all
select 'when' union all
select 'where' union all
select 'while' union all
select 'whip' union all
select 'whistle' union all
select 'white' union all
select 'who' union all
select 'why' union all
select 'wide' union all
select 'will' union all
select 'wind' union all
select 'window' union all
select 'wine' union all
select 'wing' union all
select 'winter' union all
select 'wire' union all
select 'wise' union all
select 'with' union all
select 'woman' union all
select 'wood' union all
select 'wool' union all
select 'word' union all
select 'work' union all
select 'worm' union all
select 'wound' union all
select 'writing' union all
select 'wrong' union all
select 'year' union all
select 'yellow' union all
select 'yes' union all
select 'yesterday' union all
select 'you' union all
select 'young' union all
select 'win' union all select 'fox'

set nocount off

step 2: here is the actual solution:



--the input:

declare @number varchar(20);
declare @MaxNumericDigits int;

set @MaxNumericDigits = 1; -- only allow 1 digit numbers to be returned
set @number = '642-394-6369'

-- our numbers lookup table:

create table Numbers(Number int, Letter char(1))
insert into Numbers
select 2,'a' union select 2,'b' union select 2,'c' union
select 3,'d' union select 3,'e' union select 3,'f' union
select 4,'g' union select 4,'h' union select 4,'i' union
select 5,'j' union select 5,'k' union select 5,'l' union
select 6,'m' union select 6,'n' union select 6,'o' union
select 7,'p' union select 7,'q' union select 7,'r' union select 7,'s' union
select 8,'t' union select 8,'u' union select 8,'v' union
select 9,'w' union select 9,'x' union select 9,'y' union select 9,'z' 

declare @i int;
declare @c int;

-- our "work" table:

create table tmp(Position int, Digit int, CurrWord varchar(20), Result varchar(100), Numbers int)

set @number = replace(@number,'-','')

-- get things started:

insert into tmp select 0,0, '','',0

-- and off we go:

set @i= 1;
while (@i <= len(@number))
begin
	set @c = convert(int, substring(@number,@i,1))

        -- add new letters for words that are forming:

	insert into tmp  (Position, Digit, CurrWord, Result, Numbers)
	select distinct @i, @c,
		case when CurrWord + Letter = Word then '' else CurrWord + Letter end,
		Result + Letter,
		0
	from
		Numbers N 
 	inner join tmp on tmp.Position = (@i-1)
	inner join Words W on W.Word LIKE CurrWord + Letter + '%'
	where N.Number = @c

        -- Add numbers as well, but only if a word is previously complete:

	insert into tmp (Position, Digit, CurrWord, Result, Numbers)
	select distinct @i, @c, '', Result + convert(char(1), @c), tmp.Numbers + 1 
	from tmp
	where tmp.Position = (@i-1) and (tmp.Numbers < @MaxNumericDigits) and
		(exists (select 1 from Words where Words.Word = CurrWord) or CurrWord = '')

	set @i = @i + 1
end

-- and the answer
select Result
from tmp
where (Position=len(@number)) and (exists (select 1 from Words where Words.Word = CurrWord) or CurrWord = '')
order by Result

go
drop table Numbers
drop table tmp
--drop table Words  -- uncomment this out to clean it all up when you are done

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-09 : 11:14:17

The INSERT is exactly the same as Jeff's so I cut a bunch out, to save post space.
You will have to copy the INSERT from his post, to get the dictionary data.
The CREATE TABLE is a bit different so You will have to use this one.

At first I had some problems when a word could appear several times in the phonenumber.
Actually i stumbled on this solution while trying to solve that problem.
(by using STUFF iof REPLACE)

-- We need a helper function to work out the number for a phrase
-- e.g. "about" = "22688"
create function dbo.fnGetNr(@word varchar(15))
returns varchar(15)
as
begin
	declare @nr varchar(15); set @nr = ''
	declare @i tinyint; set @i = 1
	while @i <= len(@word)
	begin
  		set @nr = @nr +
		case
			when substring(@word,@i,1) in('a','b','c') then '2'
			when substring(@word,@i,1) in('d','e','f') then '3'
			when substring(@word,@i,1) in('g','h','i') then '4'
			when substring(@word,@i,1) in('j','k','l') then '5'
			when substring(@word,@i,1) in('m','n','o') then '6'
			when substring(@word,@i,1) in('p','q','r','s') then '7'
			when substring(@word,@i,1) in('t','u','v') then '8'
			when substring(@word,@i,1) in('w','x','y','z') then '9' end
  		set @i = @i + 1
 	end
	return @nr
end
go

-- This is the same table as Jeff's, but with a nbr column that contains the word's equiv phonenbr
create table Words(Word varchar(15) primary key, nbr varchar(15) null)
set nocount on
insert into Words (Word)
select 'a' union all
select 'able' union all
.
.
-- ... and abunch that You will have to cut from Jeff's post
.
.
select 'win' union all
select 'fox'

-- Update the helper column in Words using the helper function
update Words set nbr = dbo.fnGetNr(Word)
alter table Words alter column nbr varchar(15) not null -- feels much better now

set nocount off
go


/* START WORKING */
-- create working table
create table #phrases(phrase varchar(15))

declare @phonenumber varchar(15)
set @phonenumber = '6423946369'
-- set @phonenumber = '2583268853' --("bluebottle")

truncate table #phrases	-- clear table
EDIT: Sorry, had references to old dictionary table
insert #phrases(phrase) values(@phonenumber) -- a number is part of itself
while @@rowcount > 0
	insert #phrases(phrase)
	select distinct stuff(phrase,charindex(nbr,phrase),len(Word),Word)
	from Words join #phrases on charindex(nbr,phrase) > 0
	where stuff(phrase,charindex(nbr,phrase),len(Word),Word) not in(select phrase from #phrases)

-- the result
select phrase from #phrases order by 1

-- cleanup
drop table #phrases
go
/* END WORKING */

drop table Words
go
drop function dbo.fnGetNr
go

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-09 : 11:50:10

Compared to Jeff's solution I find only 172 answers, (Jeff 184)

These are missing:
It's something with that 'I' occurring in pos 2 + 6, maybe I can fix it...
Result
------------------------------
64239i6369
64239i6do9
64239i6fox
64239iof69
64a39i6369
64a39i6do9
64a39i6fox
64a39iof69
64be9i6369
64be9i6do9
64be9i6fox
64be9iof69

PS.
Note to self:
Don't post challenges when You have work to do

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-10 : 09:58:48

The set-based solution:


declare @phonenumber varchar(15)
set @phonenumber = '6423946369'


--Select word From words

Declare @pattern varchar(1000)

Select @pattern = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(@phoneNumber,'2','[abc ]'),'3','[def ]'),'4','[ghi ]'),'5','[jkl ]'),'6','[mno ]'),'7','[pqrs ]'),'8','[tuv ]'),'9','[wxyz ]')
--Select @pattern

Drop Table #tempWords
Select 
	phone = @phonenumber,
	pattern = @pattern,
	word,
	pos00 = case when len(word)<=10 and left(replicate(' ',0)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos01 = case when len(word)<=9 and left(replicate(' ',1)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos02 = case when len(word)<=8 and left(replicate(' ',2)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos03 = case when len(word)<=7 and left(replicate(' ',3)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos04 = case when len(word)<=6 and left(replicate(' ',4)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos05 = case when len(word)<=5 and left(replicate(' ',5)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos06 = case when len(word)<=4 and left(replicate(' ',6)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos07 = case when len(word)<=3 and left(replicate(' ',7)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos08 = case when len(word)<=2 and left(replicate(' ',8)+word+replicate(' ',10),10) like @pattern then 1 else 0 end,
	pos09 = case when len(word)<=1 and left(replicate(' ',9)+word+replicate(' ',10),10) like @pattern then 1 else 0 end
Into #tempWords
From words
Where (left(replicate(' ',0)+word+replicate(' ',10),10) like @pattern and len(word)<=10)
or (left(replicate(' ',1)+word+replicate(' ',10),10) like @pattern and len(word)<=9)
or (left(replicate(' ',2)+word+replicate(' ',10),10) like @pattern and len(word)<=8)
or (left(replicate(' ',3)+word+replicate(' ',10),10) like @pattern and len(word)<=7)
or (left(replicate(' ',4)+word+replicate(' ',10),10) like @pattern and len(word)<=6)
or (left(replicate(' ',5)+word+replicate(' ',10),10) like @pattern and len(word)<=5)
or (left(replicate(' ',6)+word+replicate(' ',10),10) like @pattern and len(word)<=4)
or (left(replicate(' ',7)+word+replicate(' ',10),10) like @pattern and len(word)<=3)
or (left(replicate(' ',8)+word+replicate(' ',10),10) like @pattern and len(word)<=2)
or (left(replicate(' ',9)+word+replicate(' ',10),10) like @pattern and len(word)<=1)

Drop Table #wordPositions
Select * 
Into #wordPositions
From
	(
	Select phone, pattern, word, startPos = 0, endPos = len(word)-1 From #tempWords Where pos00=1 Union
	Select phone, pattern, word, startPos = 1, endPos = len(word)-0 From #tempWords Where pos01=1 Union
	Select phone, pattern, word, startPos = 2, endPos = len(word)+1 From #tempWords Where pos02=1 Union
	Select phone, pattern, word, startPos = 3, endPos = len(word)+2 From #tempWords Where pos03=1 Union
	Select phone, pattern, word, startPos = 4, endPos = len(word)+3 From #tempWords Where pos04=1 Union
	Select phone, pattern, word, startPos = 5, endPos = len(word)+4 From #tempWords Where pos05=1 Union
	Select phone, pattern, word, startPos = 6, endPos = len(word)+5 From #tempWords Where pos06=1 Union
	Select phone, pattern, word, startPos = 7, endPos = len(word)+6 From #tempWords Where pos07=1 Union
	Select phone, pattern, word, startPos = 8, endPos = len(word)+7 From #tempWords Where pos08=1 Union
	Select phone, pattern, word, startPos = 9, endPos = len(word)+8 From #tempWords Where pos09=1
	) A
	

Declare @wordCnt int
Set @wordCnt = 1

Drop Table #finalSet
Select phonePhrase=stuff(replicate(' ',10),a.startPos+1,a.endPos-a.startPos+1,a.word), wordCnt = @wordCnt
Into #finalSet
From #wordPositions a

While exists(Select * From #finalSet a Inner Join #wordPositions b on substring(a.phonePhrase,b.startPos+1,b.endPos-b.startPos+1)=Replicate(' ',b.endPos-b.startPos+1) Where a.wordCnt = @wordCnt)
Begin
	Insert Into #finalSet
	Select phonephrase = stuff(phonePhrase,b.startPos+1,b.endPos-b.startPos+1,b.word), wordCnt = @wordCnt+1
	From #finalSet a
	Inner Join #wordPositions b
	on substring(a.phonePhrase,b.startPos+1,b.endPos-b.startPos+1)=Replicate(' ',b.endPos-b.startPos+1)
	Where a.wordCnt = @wordCnt

	Set @wordCnt = @wordCnt+1
End


while exists(Select * From #finalSet Where phonePhrase like '% %')
Begin
	Update #finalSet
	Set phonephrase = stuff(phonephrase,charindex(' ',phonephrase),1,substring(@phonenumber,charindex(' ',phonephrase),1))
	From #finalSet
	Where phonePhrase like '% %'
End

EDIT: makes for better viewing 
Select * From (Select distinct * From #finalSet) A
Order By len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(phonephrase,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')) Desc

finds 183 answers... maybe someone can find the 184th for me

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-10 : 10:12:13

#184: 6423946369

Glad to see You Corey!

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 10:17:01

Why do you not consider the other solutions set-based?

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-10 : 10:25:23

Well I re-read you solution jeff and I don't think I read in depth enough the first time. I would agree that yours is set-based.

Rock (sorry

) I'm still not sure I think yours counts as set based. ultimately through use of your function you loop through each letter of each word in the dictionary. Depending on the size of the dictionary that could become a problem. Granted that you only do this once and save the result as part of the table, it still seems awkward to me.

I would personally vote for Jeff's solution now that I have re-read it.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources